Posts tagged: variable

Use SUMIF on a column defined by MATCH

This is a good alternative for the INDEX function combined with MATCH, when your data source consists of non unique values, and you need a sum instead of a specific value.

As the SUMIF does not work with column numbers (as in 1,2,3) but with ranges (A:A,B:B,C:C) I just create a list inside the document for translation.

SUMIF Columns
1 A:A
2 B:B
3 C:C
4 D:D

And so on for as many columns as needed. Name this list ‘FINDCOL’.

Now in the formula I use the VLOOKUP to get a MATCH, and pull the result as an INDIRECT to make it work, like this:

(In this example I use A1 for my criteria, and A:A as my find range, and I need the sum of the values in the column which has “Result” as a header name in row 1:1)

=SUMIF(A:A;A1;INDIRECT(VLOOKUP(MATCH("Result";1:1;0);FINDCOL;2;0)))

Ultimate Index: combine (nest) INDEX and MATCH..

11

1. INDEX formula:
=INDEX(RANGE,ROWnumber,COLUMNnumber)

So in the example above, to be able see the price for 20 apples, you use:
=INDEX(B2:D4,1,2) ==> Result is $2

2. MATCH formula:
=MATCH(LOOKUPValue,LOOKUPArray,MATCHType)

So in the example above, to know the column for Apple, you use:
=MATCH(“Apple”,B1:D1,0) ==> Result is 1

3. Using names:
Using names makes the formula easy to read, and very flexible. If your data sheet gets more columns or rows, it doesn’t matter if you set up the formula like this:

Define names: (insert, name, define)
Data –> =Sheet1!A1:Sheet1!AD20000
DataRow –> =Sheet1!1:1
DataCol –> =Sheet1!A:A

4. Combine the formulas
=INDEX(Data,MATCH(“Apple”,DataCol,0),MATCH(20,DataRow,0))

Ofcourse you can use input fields for your criteria on another sheet, because of the names you defined on the data sheet, you can put these on any other sheet you want!
Let’s say you put your fruit type in A1, and the number of fruit in A2, and you want A3 to show the price. You put this formula in A3:
=INDEX(Data,MATCH(A2,DataCol,0),MATCH(A1,DataRow,0))