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.
This is a discussion on Count unique text values based on condition in another column within the Excel Questions forums, part of the Question Forums category; Hello, I need to count uniques text values in a column that contains names. But I only need to count ...
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?
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
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...
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...
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.
Assuming too much and qualifying too much are two faces of the same problem.
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
Like this thread? Share it with others