LinkBack URL
About LinkBacks
I am trying to count how many ids an employee holds. Please see attached. So if one employee has three different ids, it should say 3, and if they have only 1 then 1.
Thanks,
Rae
I am trying to count how many ids an employee holds. Please see attached. So if one employee has three different ids, it should say 3, and if they have only 1 then 1.
Thanks,
Rae
It depends on what you want to happen in the case of rows 9 & 10. Shold they be 1 or 2??
If 2:
=COUNTIF(B:B,B2)
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: Lá fhéile Pádraig sona dhaoibh
...and if they should return 1:
=SUM(INDEX(($B$2:$B$15=B2)/COUNTIFS($B$2:$B$15,$B$2:$B$15&"",$A$2:$A$15,$A$2:$A$15&""),0))
it should be 1. Basically trying to count which employee has multiple ids. So if they have two unique ids, it should say 2. Sorry about the confusion.
Cool. Great. Thanks so much.. Let me populate this into my data file. will report back if there's any issue.
I've got about 32,000 rows to calculate, so excel if frozenWill wait for it to populate the formula.
Hi. This is VERY fast with 32,000 rows... It took about 90 seconds to calculate all 32,000 rows in a sample sheet
=SUM(IF(FREQUENCY(IF(($B$2:$B$32000=B2),$A$2:$A$32000),$A$2:$A$32000),1))
In YOUR version of Excel it may need to be entered as an array formula.
Thanks Glenn. The issue is that column A has id numbers stored as text and if convert it to numbers, it will remove 0s at the beginning of each id. I can copy the column to retain the original values and use a copied column and convert it to numbers in order to use the formula you recommended by using Frequency function.
That'll work. I did try atext-friendly alternative to that with FREQUENCY & MATCH, but first time my PC crashed. Then, on its second outing, the formula decided to stop working if the range was increased beyond about 1000 rows.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks