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

trbharat

New Member
Joined
Jul 4, 2019
Messages
2
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.


20172017201720172018201820182018
ProductCategoryUnit value ($)Q1Q2Q3Q4Q1Q2Q3Q4
ARegular45729423180393843
BRegular52955506726807031
CSuper84057668077625770
DSuper96551384855367779
ESuper77661394157456858
FRegular62577315338357425

<colgroup><col span="2"><col><col span="8"></colgroup><tbody>
</tbody>

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

Required result in below format

Category20172018
Regular
Super

<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>
</tbody>


Thanks for your help







 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Its always useful in these instances to supply what you think the answer should be.
 
Upvote 0
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.
 
Upvote 0
A
B
C
D
E
F
G
H
I
J
K
1
2017​
2017​
2017​
2017​
2018​
2018​
2018​
2018​
2
ProductCategoryUnit value ($)Q1Q2Q3Q4Q1Q2Q3Q4
3
ARegular
4​
57​
29​
42​
31​
80​
39​
38​
43​
4
BRegular
5​
29​
55​
50​
67​
26​
80​
70​
31​
5
CSuper
8​
40​
57​
66​
80​
77​
62​
57​
70​
6
DSuper
9​
65​
51​
38​
48​
55​
36​
77​
79​
7
ESuper
7​
76​
61​
39​
41​
57​
45​
68​
58​
8
FRegular
6​
25​
77​
31​
53​
38​
35​
74​
25​
9
10
Category
2017​
2018​
11
Regular
2757​
2867​
12
Super
5281​
5947​

<tbody>
</tbody>



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

Forum statistics

Threads
1,213,546
Messages
6,114,255
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top