Challange - Need to count # of unique names in a range WITH A CONDITION

**vij8y** · 02-11-2013, 04:14 PM

Hi There,

I have attached my file and what I need is explained in the file and I am explaining it again here:

Rules:
The count must be taken ONLY from the "Complete" status. Ignore "No Show"

E.g. If I select the country "India", I should get 227
How?
Filter 1
Filter 2
Action taken

Help
Help me with a formula in the green box

Thanks in advance.

VJ

**daddylonglegs** · 02-11-2013, 04:25 PM

Hello VJ, how do you get 227? - I count only 85 "Complete" entries where country is India - when you take the duplicates out the result is 30. For that figure try this formula

=SUM(IF(FREQUENCY(IF(A2:A15000="Complete",IF(B2:B15000=G5,IF(C2:C15000<>"",MATCH(C2:C15000,C2:C15000,0)))),ROW(C2:C15000)-ROW(C2)+1),1))

Using range names as you suggest that will be

=SUM(IF(FREQUENCY(IF(status="Complete",IF(country=G5,IF(partner<>"",MATCH(partner,partner,0)))),ROW(partner)-MIN(ROW(partner))+1),1))

.....but you need to re-define Partner because it currently refers to a single cell

confirmed with CTRL+SHIFT+ENTER

**vij8y** · 02-11-2013, 04:43 PM

Yes. The partner range is incorrect. I will re-define it.

Let me tell you how I got 227.

1. I Filtered "India" in country which brings 3200 of 13698
2. Then I filter "Complete" in status which brings 2254 of 13698
3. In this total count of partners 2254, I copied and pasted in a different sheet and removed duplicates which takes 2027 and the balance is 227.

Hope this helps.

**daddylonglegs** · 02-11-2013, 05:08 PM

OK, some of the country data has trailing spaces, as does the dropdown list in G5 so that throws off the results - if you add some TRIM functions to the formula like this

=SUM(IF(FREQUENCY(IF(status="Complete",IF(TRIM(country)=TRIM(G5),IF(partner<>"",MATCH(partner,partner,0)))),ROW(partner)-MIN(ROW(partner))+1),1))

then that should give you 227