Finding count of unique number within a specific criteria?

[Danni2004]

Looking for a formula that will help me (no pivot tables please)
I have timeclock data and need to count how many people there are within a
region.
There are two columns to use - one has the Region number in it (1 through
8), the other has SSN in it.
Because they punch in & out several times a week, there are several lines of
data for each SSN.
Region SSN
1 123-45-6789
1 123-45-6789
1 777-33-8888
1 999-77-5252
4 001-02-0003
4 001-02-0003
3 987-65-4321
3 987-65-4322
3 444-55-6666

I should get Region 1 count of 3, Region 3 count of 2, Region 4 count of 1
Any help would be greatly appreciated.
Thanks!

 

[Colin Foster]

Hi Danni,

Wonder whether the COUNTIF or SUMIF functions are the way to go?

Regards
Colin

 

[Mike H]

Hi,

With a helper column put this in B1 and drag down to the same lenth as your
data in column B
=IF(COUNTIF(B1:$B$9,B1)>1,1,0)
This formula flags duplicate cells

Then the formula
=SUMPRODUCT((A1:A9=3)*(B1:B9<>"")*(C1:C9=0))

BTW for region 3 from your posted data this will return a count of 3 because
all are unique. Change the 3 for different regions.

Mike

 

[Teethless mama]

=SUM(N(FREQUENCY(IF(Region=1,MATCH(SSN,SSN,0)),MATCH(SSN,SSN,0))>0))

ctrl+shift+enter, not just enter

 

[Teethless mama]

Another way...

=SUMPRODUCT((Region=1)/COUNTIF(SSN,SSN))

 

[Dave]

Hi TM,
Amazing! Could you please expound on how your formula works?

 

[Danni2004]

Thank you!
Both formula's worked.

Can you please clarify what ctrl-shift-enter does & why?
Thanks again!

 

[Domenic]

Can you please clarify what ctrl-shift-enter does & why?

It tells Excel that the formula needs to process array objects. See
Excel's help files on "array formulas".

Hope this helps!