Use SUMIF as VLOOKUP

This tutorial shows how to use the the SUMIF function as a VLOOKUP function allowing for greater flexibility

EXCEL FORMULA 1. Use SUMIF as VLOOKUP

EXCEL

Hard coded formula
Use SUMIF as VLOOKUP
Cell reference formula
Use SUMIF as VLOOKUP
=SUMIF($B$5:$B$9,"Cheese",INDEX($C$5:$E$9,,MATCH("Shop B",$C$4:$E$4,0)))
=SUMIF($B$9:$B$13,C5,INDEX($C$9:$E$13,,MATCH(C6,$C$8:$E$8,0)))
GENERIC FORMULA

=SUMIF(range1,criteria1,INDEX(sum_range,,MATCH(criteria2,range2,0)))

ARGUMENTS
range1: One of the ranges that you want to sum by.
crtieria1: The criteria associated with range1 that is used to determine which of the cells should be summed.
range2: One of the ranges that you want to sum by.
crtieria2: The criteria associated with range2 that is used to determine which of the cells should be summed.
sum_range: The range of cells you want to sum from.

GENERIC FORMULA

=SUMIF(range1,criteria1,INDEX(sum_range,,MATCH(criteria2,range2,0)))

ARGUMENTS
range1: One of the ranges that you want to sum by.
crtieria1: The criteria associated with range1 that is used to determine which of the cells should be summed.
range2: One of the ranges that you want to sum by.
crtieria2: The criteria associated with range2 that is used to determine which of the cells should be summed.
sum_range: The range of cells you want to sum from.

EXPLANATION

This tutorial shows how to use the the SUMIF function as a VLOOKUP function. Using the SUMIF, INDEX and MATCH functions you gain greater flexibility and functionality to sum values with associated criteria. By using the VLOOKUP function you can only return the first value associated with a single criteria, whilst using this formula you can return a sum of numbers associated with multiple criteria.

Click on either the Hard Coded or Cell Reference button to view the formula that has the criteria 1 and 2 directly entered into the formula or referenced to a specific cell.

RELATED TOPICS

Related Topic Description Related Topic and Description
Two dimensional lookup with VLOOKUP and MATCH How to apply a two dimensional lookup with VLOOKUP and MATCH functions
Two dimensional lookup with INDEX and MATCH How to apply a two dimensional lookup with INDEX and MATCH functions

RELATED FUNCTIONS

Related Functions Description Related Functions and Description
SUMIF Function The Excel SUMIF function returns the sum of all numbers in a specified range based on a single criteria
MATCH Function The Excel MATCH function searches for a specified value in a range and returns its position relative to the range
INDEX Function The Excel INDEX function returns a value that is referenced from a specified range