Hi,
I have a data set with 2 columns. One column is filled with names and the other one is filled with numbers.
A B
Name 1 20
Name 1 2
Name 2 1
Name 3
Name 4 2
I would like to count how many unique names that has >0 (empty cell) . In the example above the desired result should be 3 (Name 3 has empty and Name 1 occurs twice).
Thanks
Stefan
Are you willing to use a helper colulmn? If so, paste this into cell C1 and drag down (you can later hide the column):
Then paste this where you want your count to go:
Please Login or Register to view this content.
Using your example provided, I was able to replicate a count of 3. Hope this helps!Please Login or Register to view this content.
Spread the love, add to the Rep
"None of us are as smart as all of us."
Try this
=SUMPRODUCT((A1:A5<>"")*(B1:B5>0)/COUNTIFS(A1:A5,A1:A5,B1:B5,B1:B5&""))
Row\Col Name 1 Name 1 Name 2 Name 3 Name 4
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
try this
=SUM((FREQUENCY(INDEX((B1:B6>0)*MATCH(A1:A6,A1:A6,0),0),ROW(A1:INDEX(A:A,ROWS(A1:A6))))>0)*1)
or
below array formula
=SUM(INDEX(IFERROR((B1:B6>0)/COUNTIFS(A1:A6,A1:A6,B1:B6,">"&0),0),0)) please confirm with Shift+Ctrl+Enter
Samba
Say thanks to those who have helped you by clicking Add Reputation star.
Try this.. as array formula
PHP Code:=Sum(if(Frequency(if(B1:B6,MATCH(A1:A6,A1:A6,0)),if(B1:B6,MATCH(A1:A6,A1:A6,0),0)),1))
Regards,
Vikas Gautam
Excel-buzz.blogspot.com
Excel is not a matter of Experience, its a matter of Application.
Say Thanks, Click * Add Reputation
Thanks. I used nflsales array formula and it works. I realised it is important that the lower limit (here A6,B6) is defined correct else it will be wrong. Since I will add rows I hoped to use a lower limit much lower so I guaranteed to include all rows. It does not seem to work. So I will just need to be more careful.
Thanks again :-)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks