Count unique values based on criteria

[leterrier]

Dear Friends,


I have two columns of sales data A and B. Column A contains garment types e.g. Hats, Coats, Scarves etc. and each entry is unique. Column B contains colours e.g. green, red, blue etc. and contains duplicates (and possibly blanks). I wish to know how many different coloured hats have been purchased. Having trawled the web, the common result on a question like this provides a formula structured:

=SUMPRODUCT((Criteria_Range="Hats")/COUNTIF(Count_Range , Count_Range&""))

When I apply this formula, I keep getting answers there not whole numbers e.g. 1.5 or 0.8. My query cannot be that complicated, what am I doing wrong?

Can someone help? Its driving me crazy. Thank you!

 

[barry houdini]

Try using this "array formula"

=SUM(IF(FREQUENCY(IF(Criteria_Range="Hats",IF(Count_Range<>"",MATCH(Count_Range,Count_Range,0))),ROW(Count_Range)-MIN(ROW(Count_Range))),1))

confirmed with CTRL+SHIFT+ENTER

 

[leterrier]

Barry thanks for the swift reply, I'll give it a go and let you know, cheers!

 

[leterrier]

Barry, Thanks again, I have tried this and I get a #value! return. I note also that despite my using ctrl+shift+enter, I see no braces around the [array] formula?

Try using this "array formula"

=SUM(IF(FREQUENCY(IF(Criteria_Range="Hats",IF(Count_Range<>"",MATCH(Count_Range,Count_Range,0))),ROW(Count_Range)-MIN(ROW(Count_Range))),1))

confirmed with CTRL+SHIFT+ENTER

 

[leterrier]

Sales Data
Garment (A)	Colour (B)
hats	red
coats	green
scarves	blue
hats	green
coats	red
scarves	blue
hats	red
coats	green
Hats		blank
scarves	red
I wish to know how many different coloured hats have been sold, the answer is 2 (red and green)
The formula I need is: if the criteria in column A is "hats",
how many unique values are in column B - excluding blanks? The answer is 2 (red and green)

<colgroup><col width="107" style="width: 80pt; mso-width-source: userset; mso-width-alt: 3913;"> <col width="113" style="width: 85pt; mso-width-source: userset; mso-width-alt: 4132;"> <col width="64" style="width: 48pt;" span="6"> <tbody> </tbody>

 

[barry houdini]

I have tried this and I get a #value! return. I note also that despite my using ctrl+shift+enter, I see no braces around the [array] formula?

To "array enter" correctly do this:

Select cell with formula and press F2 key to select formula
Hold down CTRL and SHIFT keys while pressing ENTER
You should now see { and } around the formula and get a numeric result

 

[Aladin Akyurek]

See the same question at:
http://www.mrexcel.com/forum/excel-questions/800620-count-values-based-criteria-no-duplicates.html

 

[leterrier]

Barry, Thank you. Its now working perfectly!! Excellent...

To "array enter" correctly do this:

Select cell with formula and press F2 key to select formula
Hold down CTRL and SHIFT keys while pressing ENTER
You should now see { and } around the formula and get a numeric result