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))

[TABLE="class: grid"]
<tbody>[TR]
[TD]Row\Col[/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[TD]

F​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]Area[/TD]
[TD]Type[/TD]
[TD]Price[/TD]
[TD]Jan[/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]China[/TD]
[TD]Apples[/TD]
[TD]

$0.50​

[/TD]
[TD]

100​

[/TD]
[TD]

150​

[/TD]
[TD]

120​

[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]China[/TD]
[TD]Oranges[/TD]
[TD]

$0.40​

[/TD]
[TD]

510​

[/TD]
[TD]

490​

[/TD]
[TD]

520​

[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]China[/TD]
[TD]Pears[/TD]
[TD]

$0.45​

[/TD]
[TD]

300​

[/TD]
[TD]

320​

[/TD]
[TD]

310​

[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]USA[/TD]
[TD]Apples[/TD]
[TD]

$0.60​

[/TD]
[TD]

200​

[/TD]
[TD]

215​

[/TD]
[TD]

195​

[/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]USA[/TD]
[TD]Oranges[/TD]
[TD]

$0.65​

[/TD]
[TD]

600​

[/TD]
[TD]

550​

[/TD]
[TD]

560​

[/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD]USA[/TD]
[TD]Pears[/TD]
[TD]

$0.70​

[/TD]
[TD]

400​

[/TD]
[TD]

420​

[/TD]
[TD]

400​

[/TD]
[/TR]
[TR]
[TD]

8​

[/TD]
[TD]Italy[/TD]
[TD]Apples[/TD]
[TD]

$0.40​

[/TD]
[TD]

300​

[/TD]
[TD]

295​

[/TD]
[TD]

320​

[/TD]
[/TR]
[TR]
[TD]

9​

[/TD]
[TD]Italy[/TD]
[TD]Oranges[/TD]
[TD]

$0.55​

[/TD]
[TD]

200​

[/TD]
[TD]

210​

[/TD]
[TD]

215​

[/TD]
[/TR]
[TR]
[TD]

10​

[/TD]
[TD]Italy[/TD]
[TD]Pears[/TD]
[TD]

$0.60​

[/TD]
[TD]

500​

[/TD]
[TD]

480​

[/TD]
[TD]

510​

[/TD]
[/TR]
[TR]
[TD]

11​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

12​

[/TD]
[TD]jan[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

13​

[/TD]
[TD]ORANGES[/TD]
[TD]

1310​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][/tr]
[tr][td]

1​

[/td][td]Area[/td][td]Type[/td][td]Price[/td][td]Jan[/td][td]Feb[/td][td]Mar[/td][/tr]


[tr][td]

2​

[/td][td]China[/td][td]Apples[/td][td]

$0.50​

[/td][td]

100​

[/td][td]

150​

[/td][td]

120​

[/td][/tr]


[tr][td]

3​

[/td][td]China[/td][td]Oranges[/td][td]

$0.40​

[/td][td]

510​

[/td][td]

490​

[/td][td]

520​

[/td][/tr]


[tr][td]

4​

[/td][td]China[/td][td]Pears[/td][td]

$0.45​

[/td][td]

300​

[/td][td]

320​

[/td][td]

310​

[/td][/tr]


[tr][td]

5​

[/td][td]USA[/td][td]Apples[/td][td]

$0.60​

[/td][td]

200​

[/td][td]

215​

[/td][td]

195​

[/td][/tr]


[tr][td]

6​

[/td][td]USA[/td][td]Oranges[/td][td]

$0.65​

[/td][td]

600​

[/td][td]

550​

[/td][td]

560​

[/td][/tr]


[tr][td]

7​

[/td][td]USA[/td][td]Pears[/td][td]

$0.70​

[/td][td]

400​

[/td][td]

420​

[/td][td]

400​

[/td][/tr]


[tr][td]

8​

[/td][td]Italy[/td][td]Apples[/td][td]

$0.40​

[/td][td]

300​

[/td][td]

295​

[/td][td]

320​

[/td][/tr]


[tr][td]

9​

[/td][td]Italy[/td][td]Oranges[/td][td]

$0.55​

[/td][td]

200​

[/td][td]

210​

[/td][td]

215​

[/td][/tr]


[tr][td]

10​

[/td][td]Italy[/td][td]Pears[/td][td]

$0.60​

[/td][td]

500​

[/td][td]

480​

[/td][td]

510​

[/td][/tr]


[tr][td]

11​

[/td][td] [/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr][td]

12​

[/td][td]jan[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr][td]

13​

[/td][td]oranges[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr][td]

14​

[/td][td]

704​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

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.