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

trbharat

New Member
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.

 2017 2017 2017 2017 2018 2018 2018 2018 Product Category Unit value (\$) Q1 Q2 Q3 Q4 Q1 Q2 Q3 Q4 A Regular 4 57 29 42 31 80 39 38 43 B Regular 5 29 55 50 67 26 80 70 31 C Super 8 40 57 66 80 77 62 57 70 D Super 9 65 51 38 48 55 36 77 79 E Super 7 76 61 39 41 57 45 68 58 F Regular 6 25 77 31 53 38 35 74 25

<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

 Category 2017 2018 Regular Super

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

Thanks for your help

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
Its always useful in these instances to supply what you think the answer should be.

steve the fish

Well-known Member
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
 A​ B​ C​ D​ E​ F​ G​ H​ I​ J​ K​ 1​ 2017​ 2017​ 2017​ 2017​ 2018​ 2018​ 2018​ 2018​ 2​ Product Category Unit value (\$) Q1 Q2 Q3 Q4 Q1 Q2 Q3 Q4 3​ A Regular 4​ 57​ 29​ 42​ 31​ 80​ 39​ 38​ 43​ 4​ B Regular 5​ 29​ 55​ 50​ 67​ 26​ 80​ 70​ 31​ 5​ C Super 8​ 40​ 57​ 66​ 80​ 77​ 62​ 57​ 70​ 6​ D Super 9​ 65​ 51​ 38​ 48​ 55​ 36​ 77​ 79​ 7​ E Super 7​ 76​ 61​ 39​ 41​ 57​ 45​ 68​ 58​ 8​ F Regular 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
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))

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