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

Code
``=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

Could you list out the criteria and possibly attach an example workbook?

Thanks!

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

Files

• Re: Count Unique Names for Multiple criteria

Sorry I should have said, I used the formulae

Code
``=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&""))``

• Re: Count Unique Names for Multiple criteria

Sorry for the delay I had a meeting

Try this:
{=SUM(IF(FREQUENCY(IF('YR2011'!V2:V13>B2,IF('YR2011'!V2:V13<=C2,IF('YR2011'!BP2:BP13<>"",MATCH('YR2011'!C2:C13,'YR2011'!C2:C13,1)))),MATCH('YR2011'!C2:C13,'YR2011'!C2:C13,1)),1))}

This is an array formula so do not copy the brackets, and press Cntrl+Shft+Enter when you place this into the cell.

• Re: Count Unique Names for Multiple criteria

Hello again, ignore my first formula, I have been looking at it and it has been having issues for some reason. Try this as an array formula (cntrl+shift+enter):

=SUM(IF(FREQUENCY(IF('YR2011'!D2:D1687<=B2,IF('YR2011'!E2:E1687>0,MATCH('YR2011'!C2:C1687,'YR2011'!C2:C1687,0))),MATCH('YR2011'!C2:C1687,'YR2011'!C2:C1687,0)),1))

I took your example workbook and renamed the sheet to YR2011. This formula is set to work for the example you provided.

Cells B2 in a different sheet I set = 30. The answer here is 27, which is correct when I filter all length for <= 30 and sales <>0. Then I take column C copy it to a new sheet and remove duplicates.

Let me know if you have any questions.

Sincerely,
-Max

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

Code
``=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]

Images

• Re: Count Unique Names for Multiple criteria

Glad I can help! Let me know if you have any questions.

Sincerely,
-Max

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!