SumProduct Based on a Column Criteria

[jessicahittle]

Hi All -

I need help with creating a SumProduct formula that will utilize different columns (Based on the selected month) to populate the total cost.

Below is an example of what I am trying to achieve. The data setup cannot be changed at it is used a wide set of reports.

Units
Area Type Price Jan Feb Mar
China Apples $0.50 100.00 150.00 120.00
China Oranges $0.40 510.00 490.00 520.00
China Pears $0.45 300.00 320.00 310.00
USA Apples $0.60 200.00 215.00 195.00
USA Oranges $0.65 600.00 550.00 560.00
USA Pears $0.70 400.00 420.00 400.00
Italy Apples $0.40 300.00 295.00 320.00
Italy Oranges $0.55 200.00 210.00 215.00
Italy Pears $0.60 500.00 480.00 510.00

Feb
Apples ? Total Cost

 

[Weazel]

maybe something like...

B13=SUMPRODUCT(D2:F10,(B2:B10=A13)*(D1:F1=A12))

Row\Col	A​	B​	C​	D​	E​	F​
1​	Area	Type	Price	Jan	Feb	Mar
2​	China	Apples	$0.50​	100​	150​	120​
3​	China	Oranges	$0.40​	510​	490​	520​
4​	China	Pears	$0.45​	300​	320​	310​
5​	USA	Apples	$0.60​	200​	215​	195​
6​	USA	Oranges	$0.65​	600​	550​	560​
7​	USA	Pears	$0.70​	400​	420​	400​
8​	Italy	Apples	$0.40​	300​	295​	320​
9​	Italy	Oranges	$0.55​	200​	210​	215​
10​	Italy	Pears	$0.60​	500​	480​	510​
11​
12​	jan
13​	ORANGES	1310​

<tbody>
</tbody>

 

[Aladin Akyurek]

Row\Col	A​	B​	C​	D​	E​	F​
1​	Area	Type	Price	Jan	Feb	Mar
2​	China	Apples	$0.50​	100​	150​	120​
3​	China	Oranges	$0.40​	510​	490​	520​
4​	China	Pears	$0.45​	300​	320​	310​
5​	USA	Apples	$0.60​	200​	215​	195​
6​	USA	Oranges	$0.65​	600​	550​	560​
7​	USA	Pears	$0.70​	400​	420​	400​
8​	Italy	Apples	$0.40​	300​	295​	320​
9​	Italy	Oranges	$0.55​	200​	210​	215​
10​	Italy	Pears	$0.60​	500​	480​	510​
11​
12​	jan
13​	oranges
14​	704​

In A14 enter:

=SUMPRODUCT(INDEX($D$2:$F$10,0,MATCH(A12,$D$1:$F$1,0)),--($B$2:$B$10=A13),$C$2:$C$10)

This differs from the suggestion Weazel posted.