SUMPRODUCT with multiple criteria in Rows and Columns (Still function as SUMPRODUCT instead of SUM or COUNT)

[trbharat]

Hi,

I have a query related to SUMPRODUCT with multiple criteria in Rows and Columns.
Using the below example to explain the problem.






I have data of sales figures of various products belonging to different categories for different quarters over a few years. Am trying to calculate the total Value for each category for a full year.


[TABLE="width: 725"]
<colgroup><col span="2"><col><col span="8"></colgroup><tbody>[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]2017[/TD]
[TD]2017[/TD]
[TD]2017[/TD]
[TD]2017[/TD]
[TD]2018[/TD]
[TD]2018[/TD]
[TD]2018[/TD]
[TD]2018[/TD]
[/TR]
[TR]
[TD]Product[/TD]
[TD]Category[/TD]
[TD]Unit value ($)[/TD]
[TD]Q1[/TD]
[TD]Q2[/TD]
[TD]Q3[/TD]
[TD]Q4[/TD]
[TD]Q1[/TD]
[TD]Q2[/TD]
[TD]Q3[/TD]
[TD]Q4[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Regular[/TD]
[TD]4[/TD]
[TD]57[/TD]
[TD]29[/TD]
[TD]42[/TD]
[TD]31[/TD]
[TD]80[/TD]
[TD]39[/TD]
[TD]38[/TD]
[TD]43[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]Regular[/TD]
[TD]5[/TD]
[TD]29[/TD]
[TD]55[/TD]
[TD]50[/TD]
[TD]67[/TD]
[TD]26[/TD]
[TD]80[/TD]
[TD]70[/TD]
[TD]31[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]Super[/TD]
[TD]8[/TD]
[TD]40[/TD]
[TD]57[/TD]
[TD]66[/TD]
[TD]80[/TD]
[TD]77[/TD]
[TD]62[/TD]
[TD]57[/TD]
[TD]70[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]Super[/TD]
[TD]9[/TD]
[TD]65[/TD]
[TD]51[/TD]
[TD]38[/TD]
[TD]48[/TD]
[TD]55[/TD]
[TD]36[/TD]
[TD]77[/TD]
[TD]79[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD]Super[/TD]
[TD]7[/TD]
[TD]76[/TD]
[TD]61[/TD]
[TD]39[/TD]
[TD]41[/TD]
[TD]57[/TD]
[TD]45[/TD]
[TD]68[/TD]
[TD]58[/TD]
[/TR]
[TR]
[TD]F[/TD]
[TD]Regular[/TD]
[TD]6[/TD]
[TD]25[/TD]
[TD]77[/TD]
[TD]31[/TD]
[TD]53[/TD]
[TD]38[/TD]
[TD]35[/TD]
[TD]74[/TD]
[TD]25[/TD]
[/TR]
</tbody>[/TABLE]

I want to get the result without using any helper columns (adding full year numbers in helper columns).

Required result in below format

[TABLE="width: 192"]
<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl66, width: 64"]Category[/TD]
[TD="class: xl66, width: 64"]2017[/TD]
[TD="class: xl66, width: 64"]2018[/TD]
[/TR]
[TR]
[TD="class: xl66"]Regular[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl66"]Super[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
</tbody>[/TABLE]


Thanks for your help

 

[steve the fish]

Its always useful in these instances to supply what you think the answer should be.

 

[steve the fish]

By looking you maybe are multiplying by the unit value, even though you dont mention, so maybe:

=SUMPRODUCT(($D$1:$K$1=B$11)*$D$3:$K$8*$C$3:$C$8*($B$3:$B$8=$A12))

Adjust ranges to suit.

 

[MARZIOTULLIO]

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

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[TD]

F​

[/TD]
[TD]

G​

[/TD]
[TD]

H​

[/TD]
[TD]

I​

[/TD]
[TD]

J​

[/TD]
[TD]

K​

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

1​

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

2017​

[/TD]
[TD]

2017​

[/TD]
[TD]

2017​

[/TD]
[TD]

2017​

[/TD]
[TD]

2018​

[/TD]
[TD]

2018​

[/TD]
[TD]

2018​

[/TD]
[TD]

2018​

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

2​

[/TD]
[TD]Product[/TD]
[TD]Category[/TD]
[TD]Unit value ($)[/TD]
[TD]Q1[/TD]
[TD]Q2[/TD]
[TD]Q3[/TD]
[TD]Q4[/TD]
[TD]Q1[/TD]
[TD]Q2[/TD]
[TD]Q3[/TD]
[TD]Q4[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]A[/TD]
[TD]Regular[/TD]
[TD]

4​

[/TD]
[TD]

57​

[/TD]
[TD]

29​

[/TD]
[TD]

42​

[/TD]
[TD]

31​

[/TD]
[TD]

80​

[/TD]
[TD]

39​

[/TD]
[TD]

38​

[/TD]
[TD]

43​

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

4​

[/TD]
[TD]B[/TD]
[TD]Regular[/TD]
[TD]

5​

[/TD]
[TD]

29​

[/TD]
[TD]

55​

[/TD]
[TD]

50​

[/TD]
[TD]

67​

[/TD]
[TD]

26​

[/TD]
[TD]

80​

[/TD]
[TD]

70​

[/TD]
[TD]

31​

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

5​

[/TD]
[TD]C[/TD]
[TD]Super[/TD]
[TD]

8​

[/TD]
[TD]

40​

[/TD]
[TD]

57​

[/TD]
[TD]

66​

[/TD]
[TD]

80​

[/TD]
[TD]

77​

[/TD]
[TD]

62​

[/TD]
[TD]

57​

[/TD]
[TD]

70​

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

6​

[/TD]
[TD]D[/TD]
[TD]Super[/TD]
[TD]

9​

[/TD]
[TD]

65​

[/TD]
[TD]

51​

[/TD]
[TD]

38​

[/TD]
[TD]

48​

[/TD]
[TD]

55​

[/TD]
[TD]

36​

[/TD]
[TD]

77​

[/TD]
[TD]

79​

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

7​

[/TD]
[TD]E[/TD]
[TD]Super[/TD]
[TD]

7​

[/TD]
[TD]

76​

[/TD]
[TD]

61​

[/TD]
[TD]

39​

[/TD]
[TD]

41​

[/TD]
[TD]

57​

[/TD]
[TD]

45​

[/TD]
[TD]

68​

[/TD]
[TD]

58​

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

8​

[/TD]
[TD]F[/TD]
[TD]Regular[/TD]
[TD]

6​

[/TD]
[TD]

25​

[/TD]
[TD]

77​

[/TD]
[TD]

31​

[/TD]
[TD]

53​

[/TD]
[TD]

38​

[/TD]
[TD]

35​

[/TD]
[TD]

74​

[/TD]
[TD]

25​

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

9​

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

10​

[/TD]
[TD]Category[/TD]
[TD]

2017​

[/TD]
[TD]

2018​

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

11​

[/TD]
[TD]Regular[/TD]
[TD]

2757​

[/TD]
[TD]

2867​

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

12​

[/TD]
[TD]Super[/TD]
[TD]

5281​

[/TD]
[TD]

5947​

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



B11=IF(AND($A11<>"",B$10<>""),SUMPRODUCT(($B$3:$B$8=$A11)*($D$1:$K$1=B$10)*($D$3:$K$8)*($C$3:$C$8)),"")

Copy across and down

 

[trbharat]

By looking you maybe are multiplying by the unit value, even though you dont mention, so maybe:

=SUMPRODUCT(($D$1:$K$1=B$11)*$D$3:$K$8*$C$3:$C$8*($B$3:$B$8=$A12))

Adjust ranges to suit.

Thanks a lot Steve. Worked fine

Worked with selecting the range of cells beyond the last row, but I tried freezing the whole column as mentioned below which didn't work.

This didn't work.
=SUMPRODUCT(($D$1:$K$1=B$11)*$D:$K*$C:$C*($B:$B=$A12))