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!

Hi Danni,

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

Regards
Colin

"Danni2004" wrote:

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!

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

"Danni2004" wrote:

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!

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

ctrl+shift+enter, not just enter


"Danni2004" wrote:

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!

Another way...

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


"Danni2004" wrote:

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!

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


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

Thank you!
Both formula's worked.

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

"Teethless mama" wrote:

Another way...

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


"Danni2004" wrote:

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!

In article ,
Danni2004 wrote:

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!