# Thread: SUMPRODUCT with multiple criteria in Rows and Columns (Still function as SUMPRODUCT instead of SUM or COUNT) Thanks:  2 Post #5304338 (1)Post #5304414 (1) Likes:  2 Post #5304338 (1)Post #5304414 (1)

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

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

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

Thanks for your help

2. ## Re: SUMPRODUCT with multiple criteria in Rows and Columns (Still function as SUMPRODUCT instead of SUM or COUNT)

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

3. ## Re: SUMPRODUCT with multiple criteria in Rows and Columns (Still function as SUMPRODUCT instead of SUM or COUNT)

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.

4. ## Re: SUMPRODUCT with multiple criteria in Rows and Columns (Still function as SUMPRODUCT instead of SUM or COUNT)

 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

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

5. ## Re: SUMPRODUCT with multiple criteria in Rows and Columns (Still function as SUMPRODUCT instead of SUM or COUNT)

Originally Posted by 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.

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

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•