Multi Criterion & Sumproduct

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

The second segment has sales records

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

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

Thanks in advance!

Noaman 

Thanks

Noaman

Hi Noaman

Sustitute the range where you have your seelctions, Yes and No for the word "include" in the following formula

=SUMPRODUCT(($C$16:$C$20=D26)*($D$16:$D$20=E26)*(include="Yes"),$F$16:$F$20)

Thanks Roger!

Works as a charm - appreciate the quick response too

Ta,

N.

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(H3:H11243)>0,MATCH(H3:H11243,H3:H11243,0),""), IF(LEN(H3:H11243)>0,MATCH(H3:H11243,H3:H11243,0),""))>0,1)) as an array....

from the formula given by Roger:

=SUMPRODUCT(($C$16:$C$20=D26)*($D$16:$D$20=E26)*(include="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?

Thanks in advance

Are you sure that formula works? it is okay for the 2010, 1, but 2010, 2 returns 23 for me, whereas it should be 36.

The range should be two columns: category & include. Furthermore, the categories have to be sorted alphabetically.

Does that answer my question?

Yes, it should be 36. You would be getting 23 if you havent sorted the categories alphabetically. So, to get the correct answer and for the formula to work properly (ie to get correct answer as 36), one should sort the categories alphabetically.

Sorry, I am missing something here. You say the answer should be 36, the formula returns 23, but you say it works. It does not compute!

Hey Bob,

Turn all the selection off, and then turn then back on - it should work.

I'll paste my tables once again.

D2:F9

C14:F20

D24:F28

Formula against F26: =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)
Copy paste the F26 formula to F27 & F28 and re-run all selections by turning all off and then back on again.