Sumif/sumproduct help!

[KATYCUSHEN]

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

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Item[/TD]
[TD]Budget v1[/TD]
[TD]Budget v2[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Item A[/TD]
[TD]50[/TD]
[TD]55[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Item B[/TD]
[TD]60[/TD]
[TD]65[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Item C[/TD]
[TD]70[/TD]
[TD]75[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Item D[/TD]
[TD]80[/TD]
[TD]85[/TD]
[/TR]
</tbody>[/TABLE]

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

 

[kweaver]

Welcome to the lounge.

Do you mean something like this:

=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:

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

 

[kweaver]

More general it would look like this:

Excel 2010
	A	B	C
1	Item C	Budget v2	75
2	Item A	Budget v2	55
3	Item B	Budget v1	60
NewBudget

Cell Formulas
Range	Formula
C1	=INDEX(Budget!$A$1:$C$500,MATCH(A1,Budget!$A$1:$A$500,0),MATCH(B1,Budget!$A$1:$C$1,0))

 

[KATYCUSHEN]

Solved it, thanks so much!