Count Condition Unique Items

Hi all,

Can someone please help me with formulas to calculate the # of Unique Customers, #of Items, and Amount for Q1, Q2, Q3 and Q4 for the year in A2 (which contains a drop-down list).

Please see attached sample workbook. (I can’t figure out how to combine the formulas for count unique items with a specific quarter.)

Thank you,
Gos-C

Corrected Unique count, array works for all entries now.

when editing formulas in column H, they are array formulas, press CTRL-SHIFT-ENTER instead of just ENTER when done editing.

JB,

All the formulas are showing the #VALUE! error.

I modified my spreadsheet to include another year's dates. I need the report to change to show the stats for whatever year is selected/entered in A2.

I tried the following array formula but it not working.

Please Login or Register  to view this content.

herbdsy, thanks for your solution, but the user does not want to use a PivotTable.

Thank you,
Gos-C

When you open my attachment above, in post #2, do you see any errors at that moment in the chart? You shouldn't they're all working.

If you're adapting the formulas for use in another sheet, then there are probably simple translation errors. And as I warned, when you use the formulas in the tan fields, those are array formula, you must type them in then press CTRL-SHIFT-ENTER or they won't work. Also, all 12 formulas are different, no copying down.

Post up a larger set of data if you want, or tell me what the actual ranges are and I'll adjust the formulas for you.

BTW, that attachment above in post #4 appears to be an error....wrong book maybe? Similar name...

The solution JBeaucaire presented is a lot smarter than mine. The reason it is not working for everybody is your country settings. Change this part of you formula, to match your country settings:

"M/D/YYYY"

= SUM(IF(FREQUENCY(IF(MONTH(TEXT($L$7:$L$21,"M/D/YYYY"))={4\5\6},$C$7:$C$21),$C$7:$C$21)>0,1))

Hi Steve R,

Yes, that did it! I changed "M/D/YYYY" to "D/M/YYYY" and it worked.

Thanks a lot Steve R and JBeaucaire.

However, the formula does not distinguish the year. I want the formula to use the year in A2.

Now, only if I can get it to give the stats only for the specified year I would be fine.

Gos-C

Hi martindwilson,

See attachment. The report includes both 2007 and 2008 numbers, but I want the numbers for the year in A2 only.

this will return sum jan,feb,march 2008 (ist quarter if thats how your quarters run otherwise adjust dates to suit
=SUMPRODUCT(($L$7:$L$36>=DATE($A$2,1,1))*($L$7:$L$36<=DATE($A$2,3,31)),
($M$7:$M$36))

Yes! # of Items and Amount are now calculating correctly. But the # of Unique Customers needs to reference the year in A2 as well. See attachment.

Thank you, martindwilson

Regards,
Gos-C

I figured it out - see attachment.

Thank you, everyone, for you help.

Regards,
Gos-C

Nice well done.

Last suggestion, change your YEAR range on sheet 3 to actually just show years so your drop down on Sheet2 is cleaner, formatted as normal numbers:

YEAR
2005
2006
2007
2008
2009
2010
2011
2012

Then your drop box not only looks normal showing only years, you simplify out a calculation in all your formulas, every place that used to have to convert A2 to a year value can now reference it directly, for instance the formula in J2 simplifies down a little to:

=SUMPRODUCT(($L$8:$L$37>=DATE($A$2,1,1))*($L$8:$L$37<=DATE($A$2,3,31)),($M$8:$M$37))

It used to say: DATE(YEAR($A$2),1,1)

Great! Thanks for the tip, JBeaucaire.

Keep "excel-ing"

Gos-C

If that takes care of your need, be sure to EDIT your original post (Go Advanced) and mark the PREFIX box [SOLVED]