Functions and Formulas

Re: Multi Criterion & Sumproduct

noaman — Wed, 23 Mar 2011 03:56:25 GMT

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

Selection
#	Category	Include?
1	1- Electricals	Yes
2	2- Paper	Yes
3	3- Food	Yes
4	4- Beverages	No
5	5- Pets	No
6	6- Cosmetics	No

C14:F20

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

D24:F28

Summary / Report
Year	Month	Quantity
2010	1	439
2010	2	36
2010	3	0

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.

Re: Multi Criterion & Sumproduct

Bob Phillips — Tue, 22 Mar 2011 10:32:52 GMT

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!

Re: Multi Criterion & Sumproduct

noaman — Tue, 22 Mar 2011 03:36:54 GMT

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.

Re: Multi Criterion & Sumproduct

Bob Phillips — Mon, 21 Mar 2011 23:20:02 GMT

Does that answer my question?

Re: Multi Criterion & Sumproduct

noaman — Mon, 21 Mar 2011 10:06:55 GMT

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

Re: Multi Criterion & Sumproduct

Bob Phillips — Mon, 21 Mar 2011 09:56:07 GMT

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.

Re: Multi Criterion & Sumproduct

noaman — Thu, 17 Mar 2011 10:12:46 GMT

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

Re: Multi Criterion & Sumproduct

noaman — Tue, 15 Mar 2011 09:33:55 GMT

Thanks Roger!

Works as a charm - appreciate the quick response too

Ta,

Re: Multi Criterion & Sumproduct

Roger Govier — Tue, 15 Mar 2011 09:04:15 GMT

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)

Multi Criterion & Sumproduct

noaman — Tue, 15 Mar 2011 08:13:13 GMT

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 sheet

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