Sumif/sumproduct help!

[KATYCUSHEN]

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

<tbody>
</tbody>

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!