# Multi criteria sumproduct name range query

1. ## Multi criteria sumproduct name range query

Hello,

Would highly like if you could shed some light on how I can accomplish this.

My sheet has three segments. The first being a selection for the users (the user can choose which category they want to view the reports for):
Selection
# Category Include?
1 Electricals Yes
2 Paper Yes
3 Food Yes
4 Beverages No
5 Pets No
6 Cosmetics No

The second segment has sales records

Sales
Year Month Category Quantity
2010 1 Electricals 5
2010 1 Paper 434
2010 2 Electricals 23
2010 2 Food 13
2010 3 Beverages 2323

And, the third has performance summary / report based on year-month against the selected criterions in the first segment

Summary / Report
Year Month Quantity
2010 1 ???
2010 2 ???
2010 3 ???

The max I've reached for the formula against Quantity is as follows:

=SUMPRODUCT((\$C\$16:\$C\$20=D26)*(\$D\$16:\$D\$20=E26),\$F\$16:\$F\$20)

but, this doesnt consider the selection that the user has made, and as such the month-wise totals are coming up. I'm aware that I can use PivotTable with ease for this requirement, but I'd ideally prefer a non-PivotTable solution as there are many other dependencies.

Also, if the user-selected categories can be dynamically captured as a named range - could use it for several validation moving forward.

Noaman

2. ## Re: Multi criteria sumproduct name range query

Try:

=SUMPRODUCT((\$C\$16:\$C\$20=D26)*(\$D\$16:\$D\$20=E26)*(LOOKUP(\$E\$16:\$E\$20,\$E\$4:\$F\$9)="Yes"),\$F\$16:\$F\$20)

The values in E4:E9 must be sorted alphabetically though.

Dom

3. ## Re: Multi criteria sumproduct name range query

Hi Dom!

It works like a charm - thank you sooo much!!!!

Once again - thanks for the prompt response!!!

Ta,
N.

4. ## Re: Multi criteria sumproduct name range query

Hello again!

Wondering if its possible with the same example to actually count the distinct values in the given column

I'm aware that I can get distinct count (including text) by saying: =SUM(IF(FREQUENCY(IF(LEN(range:range)>0,MATCH(range:range,range:range,0),""), IF(LEN(range:range)>0,MATCH(range:range,range:range,0),""))>0,1)) as an array....

from the formula given by Dom:

=SUMPRODUCT((\$C\$16:\$C\$20=D26)*(\$D\$16:\$D\$20=E26)*(LOOKUP(\$E\$16:\$E\$20,\$E\$4:\$F\$9)="Yes"),\$F\$16:\$F\$20)

is it possible to get distinct count similar to abovementioned so that instead of getting a sum from F16:F20 --> I actually get distinct count from J16:J20?

Would this be possible without the use of macros?

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1