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







 

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,080
Office Version
365
Platform
Windows
Its always useful in these instances to supply what you think the answer should be.
 

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,080
Office Version
365
Platform
Windows
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

Well-known Member
Joined
Aug 22, 2015
Messages
767
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
 

trbharat

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

Watch MrExcel Video

Forum statistics

Threads
1,102,438
Messages
5,486,891
Members
407,570
Latest member
cannotquitexcel

This Week's Hot Topics

Top