# Thread: Sumif/sumproduct help! Thanks: 0 Likes: 0

1. ## Sumif/sumproduct help!

I am struggling with creating a formula that works for the below scenario

 A B C 1 Item Budget v1 Budget v2 2 Item A 50 55 3 Item B 60 65 4 Item C 70 75 5 Item D 80 85

I have two sheets in one workbook. One with full budget data with in this scenario a Budget v1 and Budget v2 along with individual item names

I want to set up a formula in a separate sheet to input the correct number based on two criteria, the specific item name and the specific budget number

I can get SUM IF to work if there is only one criteria but not both. I have also tried SUMPRODUCT and INDEX MATCH scenarios but not successfully!

I also want to avoid writing "Item A" as this formula will need to scan hundreds of lines of data - all individually named

All help appreciated

2. ## Re: Sumif/sumproduct help!

Welcome to the lounge.

Do you mean something like this:

Code:
`=INDEX(A1:C5,MATCH("Item C",A1:A5,0),MATCH("Budget v2",A1:C1,0))`
Where you could put the item reference in a cell and the budget reference in another and change the specific quoted values above.

The above formula would return 75.

Alternatively:

Code:
`=INDEX(Budget!A1:C5,MATCH("Item C",Budget!A1:A5,0),MATCH("Budget v2",Budget!A1:C1,0))`

3. ## Re: Sumif/sumproduct help!

More general it would look like this:

Excel 2010
ABC
1Item CBudget v275
2Item ABudget v255
3Item BBudget v160

NewBudget

Worksheet Formulas
CellFormula
C1=INDEX(Budget!\$A\$1:\$C\$500,MATCH(A1,Budget!\$A\$1:\$A\$500,0),MATCH(B1,Budget!\$A\$1:\$C\$1,0))

4. ## Re: Sumif/sumproduct help!

Solved it, thanks so much!