Count Unique Names for Multiple criteria

Hi all,

Im a bit stumped on this, and would appreciate someone telling me what I have done wrong. Basically I need to count the number of unique names after multiple criteria. I thought this would work, but it doesn't seem to give the right answer

=SUMPRODUCT(('YR2011'!V2:V1687>B2)*('YR2011'!V2:V1687<=C2)*('YR2011'!BP2:BP1687<>"")*('YR2011'!BP2:BP1687>0)*('YR2011'!$C$2:$C$1687<>"")/COUNTIFS('YR2011'!$C$2:$C$1687,'YR2011'!$C$2:$C$1687&"",'YR2011'!$V$2:$V$1687,'YR2011'!$V$2:$V$1687&"",'YR2011'!$V$2:$V$1687,'YR2011'!$BP$2:$BP$1687&""))

YR2011 Column V gives length, YR2011 Column C is the name of manufacturer which I am trying to count the number of unique values for, B2 and C2 are length that manufacturer gives which is between 20 and 30, and YR2011 column BP is the number of units sold as I only want to count manufacturers that sell anything. The data can include blanks for any criteria, but if possible this should be ignored in the calculations.

A swift response would be appreciated as I have spent all afternoon on this and im being questioned why it is so difficult!

Re: Count Unique Names for Multiple criteria

Many thanks for helping. I have attached a simple cut down version.

I need to take the data, split out into a siummary table the length into 10ft ranges (ie between 20-30,30-40,40-50ft etc etc), with sales over 0 and then count how many unique names in column B I would have for each length range. Does that make sense? Worked example

• Length 30 or less in column D gives 39 entries
• Filter on Sales over 0 in Column E gives 34 (as only interested in what is being sold)
• If I then copy and paste unique names found in column B, that gives 26 which is the answer.

The formula must be able to handle blanks without error, and be able to add other elements in (hence why I was using sumproduct). Does that make sense?

Re: Count Unique Names for Multiple criteria

Sorry I should have said, I used the formulae

=SUMPRODUCT((E2:E1687>0)*(D2:D1687>20)*(D2:D1687<=30)*(B2:B1687<>"")/COUNTIFS(B2:B1687,B2:B1687&"",E2:E1687,E2:E1687&"",D2:D1687,D2:D1687&""))

But it gives the answer as 32 not 26. Hence here asking the adults!

Re: Count Unique Names for Multiple criteria

Max,

Thank you. Was there no way of using Sumproduct? In my mind it was the way to go! I will have a play with this today and learn how it works, so thank you for helping me!

Re: Count Unique Names for Multiple criteria

Hi Max,

The numbers don't appear to be coming out correctly for the ranges?

=SUM(IF(FREQUENCY(IF('YR2011'!$V$2:$V$1687<=C2,IF('YR2011'!$V$2:$V$1687>B2,IF('YR2011'!$BP$2:$BP$1687>0,MATCH('YR2011'!$C$2:$C$1687,'YR2011'!$C$2:$C$1687,0)))),MATCH('YR2011'!$C$2:$C$1687,'YR2011'!$C$2:$C$1687,0)),1))

My summary table looks like this, however I know something isn't right as the 0-165 range isn't the sum of the other rows!

EDIT: I am an idiot! The maths is correct, many of these names are within the individual groups, so it wont "subtotal" in this way. Thank you so much for this, its right

[ATTACH=CONFIG]67928[/ATTACH]