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.

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


<TABLE style="WIDTH: 144pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=192 border=0><COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl63 id=td_post_2083318 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: black" width=64 height=20>Group</TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: black" width=64>week</TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: black" width=64>name</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: #efefef" width=64 height=20>a</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef" width=64>w1</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef" width=64>Name 1</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: #efefef" width=64 height=20>a</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef" width=64>w1</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef" width=64>Name 2</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: #efefef" width=64 height=20>a</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef" width=64>w2</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef" width=64>Name 3</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: #efefef" width=64 height=20>a</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef" width=64>w3</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef" width=64>Name 4</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: #efefef" width=64 height=20>a</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef" width=64>w4</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef" width=64>Name 5</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: #efefef" width=64 height=20>b</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef" width=64>w1</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef" width=64>Name 1</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: #efefef" width=64 height=20>b</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef" width=64>w2</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef" width=64>Name 2</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: #efefef" width=64 height=20>b</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef" width=64>w2</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef" width=64>Name 3</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: #efefef" width=64 height=20>b</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">w2</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef" width=64>Name 4</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: #efefef" width=64 height=20>b</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">w3</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef" width=64>Name 5</TD></TR></TBODY></TABLE>

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...

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))

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...

Domenic said:

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.

Click to expand...

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))

Thank you for your help.

martyna said:

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))

Thank you for your help.

Click to expand...

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?

Aladin Akyurek said:

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?

Click to expand...

Thanks for the reply,
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

martyna said:

Thanks for the reply,
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

Click to expand...

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

Aladin Akyurek said:

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

Click to expand...

Can we... --> Can we see...