Count unique text values based on condition in another column

# Thread: Count unique text values based on condition in another column

1. ## Count unique text values based on condition in another column

Hello,

I need to count uniques text values in a column that contains names.
But I only need to count the unique values that are satisfying a condition in another column.

Example:

 Group Name a Name 1 a Name 1 a Name 2 a Name 2 b Name 1 b Name 2 b Name 3 c Name 4

So I need to count unique names from group "a"

I already got the formula for counting the unique values from the whole list, and I just need to add the condition that would restrict the search only to one group (condition).

Any ideas?

2. ## Re: Count unique text values based on condition in another column

Try...

=SUM(IF(FREQUENCY(IF(A2:A100="a",IF(B2:B100<>"",MATCH("~"&B2:B100,B2:B100&"",0))),ROW(B2:B100)-ROW(B2)+1),1))

...confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Adjust the ranges, accordingly.

3. ## Re: Count unique text values based on condition in another column

Thank you,
it's working perfectly

...

now for the most difficult part...
i have a period of x weeks (4 or 5 each month)
I have to find the maximum number of unique names in one week

Example:
Group A:
- week1 = 2 unique names
- week2 = 1 unique name
- week3 = 1 unique name
- week4 = 1 unique name
Max = 2 unique names/week

Group B:
- week1 = 1 unique name
- week2 = 3 unique names
- week3 = 1 unique name
- week4 = 0 unique names
Max = 3 unique names/week

 Group week name a w1 Name 1 a w1 Name 2 a w2 Name 3 a w3 Name 4 a w4 Name 5 b w1 Name 1 b w2 Name 2 b w2 Name 3 b w2 Name 4 b w3 Name 5

How do I get to this result ?
I've found some D functions, but they only work with numbers...

Thank you...
I hope I've provided an interesting challenge for all of you...

4. ## Re: Count unique text values based on condition in another column

Assuming that A2:C11 contains the data, G2 contains "a", and G3 contains "b", try the following...

D2, confirmed with CONTROL+SHIFT+ENTER, and copied down:

=SUM(IF(FREQUENCY(IF(\$A\$2:\$A\$11=A2,IF(\$B\$2:\$B\$11=B2,IF(\$C\$2:\$C\$11<>"",MATCH("~"&\$C\$2:\$C\$11,\$C\$2:\$C\$11&"",0)))),ROW(\$C\$2:\$C\$11)-ROW(\$C\$2)+1),1))

H2, confirmed with CONTROL+SHIFT+ENTER, and copied down:

=MAX(IF(\$A\$2:\$A\$11=G2,\$E\$2:\$E\$11))

Actually, in order to reduce the number of calculations that need to take place, try the following instead...

D2, copied down:

=A2&B2

E2, confirmed with CONTROL+SHIFT+ENTER, and copied down:

=IF(ISNA(MATCH(D2,\$D\$1:D1,0)),SUM(IF(FREQUENCY(IF(\$A\$2:\$A\$11=A2,IF(\$B\$2:\$B\$11=B2,IF(\$C\$2:\$C\$11<>"",MATCH("~"&\$C\$2:\$C\$11,\$C\$2:\$C\$11&"",0)))),ROW(\$C\$2:\$C\$11)-ROW(\$C\$2)+1),1)),"")

H2, confirmed with CONTROL+SHIFT+ENTER, and copied down:

=MAX(IF(\$A\$2:\$A\$11=G2,\$E\$2:\$E\$11))

5. ## Re: Count unique text values based on condition in another column

Thank you, it works wonderfully...

I guess that your excel experience is in the region of millions of hours...

Let me know if you need more challanges...

6. ## Re: Count unique text values based on condition in another column

Originally Posted by Domenic
Try...

=SUM(IF(FREQUENCY(IF(A2:A100="a",IF(B2:B100<>"",MATCH("~"&B2:B100,B2:B100&"",0))),ROW(B2:B100)-ROW(B2)+1),1))

...confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Adjust the ranges, accordingly.
I've used this formula but i"m getting an #N/A error. When i evaluate the formula it seems to be in the Match(True,#N/A,0) area.
Since I don't exactly understand the "~" purpose in the match function I'm not sure how to fix this. Please help.

What is strange when I clidk on the Fx beside the formula bar the result displays there but in the cell i get #N/A

this is exactly what I have after adjusting my ranges:

The values in Q are text (City) and values in D are numbers (customer number) , not sure if that makes a difference. I'm looking to count # of customers in a given city, but the customer number appears many times, depending on how many times delivery took place so I need a count of the unique customer number for each city.

=SUM(IF(FREQUENCY(IF(Calculations!Q3:Q1000=Output!B19,IF(Calculations!D3:D1000<>"",MATCH("~"&Calculations!D3:D1000,Calculations!D3:D1000&"",0))),ROW(Calculations!D3:D1000)-ROW(D3)+1),1))

7. ## Re: Count unique text values based on condition in another column

Originally Posted by martyna
I've used this formula but i"m getting an #N/A error. When i evaluate the formula it seems to be in the Match(True,#N/A,0) area.
Since I don't exactly understand the "~" purpose in the match function I'm not sure how to fix this. Please help.

What is strange when I clidk on the Fx beside the formula bar the result displays there but in the cell i get #N/A

this is exactly what I have after adjusting my ranges:

The values in Q are text (City) and values in D are numbers (customer number) , not sure if that makes a difference. I'm looking to count # of customers in a given city, but the customer number appears many times, depending on how many times delivery took place so I need a count of the unique customer number for each city.

=SUM(IF(FREQUENCY(IF(Calculations!Q3:Q1000=Output!B19,IF(Calculations!D3:D1000<>"",MATCH("~"&Calculations!D3:D1000,Calculations!D3:D1000&"",0))),ROW(Calculations!D3:D1000)-ROW(D3)+1),1))

The formula, confirmed with control+shift+enter, should work as you have it.

Do you have perhaps #N/A in Calculations!Q3:Q1000 and/or in Calculations!D3:D1000 due to a look up formula?

8. ## Re: Count unique text values based on condition in another column

The formula, confirmed with control+shift+enter, should work as you have it.

Do you have perhaps #N/A in Calculations!Q3:Q1000 and/or in Calculations!D3:D1000 due to a look up formula?
I checked that and adjusted the ranges to be exact and not including blanks... etc, and it does not make a difference, I still get N/A.
I couldn't figure it out so I just added a column and did a simple comparison to the row above resulting in a 1 or 0 value, so this way I just sum up the 1s in that column to get the unique number, but it would have been nice to have the above formula work.

Thanks,
Martyna

9. ## Re: Count unique text values based on condition in another column

Originally Posted by martyna
I checked that and adjusted the ranges to be exact and not including blanks... etc, and it does not make a difference, I still get N/A.
I couldn't figure it out so I just added a column and did a simple comparison to the row above resulting in a 1 or 0 value, so this way I just sum up the 1s in that column to get the unique number, but it would have been nice to have the above formula work.

Thanks,
Martyna
Having blanks in the relevant ranges should not be a problem. Can we that comparison formula that yieds a 1 or 0?

10. ## Re: Count unique text values based on condition in another column

Having blanks in the relevant ranges should not be a problem. Can we that comparison formula that yieds a 1 or 0?
Can we... --> Can we see...

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•