Unique count formula

[Daniel Bonallack]

Would someone please explain to me how this formula works?

=SUMPRODUCT(($A$2:$A100=1)/COUNTIF($B$2:$B$100,$B$2:$B$100))

Regions are in columns A, and Social Security Numbers are in column B

This formula returns the unique number of SSN's in a given region (in this
case, region 1).

Thanks in advance
Daniel

 

[T. Valko]

Try this experiment on a small sample.

A2:A6 = 1,2,1,3,1
B2:B6 = 1,2,1,9,5

=SUMPRODUCT((A2:A6=1)/COUNTIF(B2:B6,B2:B6))

The result is 2.

Let's see how this happens. Enter these formulas:

In D2 copied down to D6: =A2=1
In E2 copied down to E6: =COUNTIF(B$2:B$6,B2)
In F2 copied down to F6: =D2/E2
In G2: =SUM(F2:F6)

Try changing the numbers in A2:A6 and B2:B6 and watch what happens.

Just some things to be aware of using that formula. If there are empty cells
in B2:B6 it'll return an error. If a ssn might be associated with more than
one region you'll get an incorrect result.

 

[Daniel Bonallack]

Thank you so much!

I understand now!

Try this experiment on a small sample.

A2:A6 = 1,2,1,3,1
B2:B6 = 1,2,1,9,5

=SUMPRODUCT((A2:A6=1)/COUNTIF(B2:B6,B2:B6))

The result is 2.

Let's see how this happens. Enter these formulas:

In D2 copied down to D6: =A2=1
In E2 copied down to E6: =COUNTIF(B$2:B$6,B2)
In F2 copied down to F6: =D2/E2
In G2: =SUM(F2:F6)

Try changing the numbers in A2:A6 and B2:B6 and watch what happens.

Just some things to be aware of using that formula. If there are empty cells
in B2:B6 it'll return an error. If a ssn might be associated with more than
one region you'll get an incorrect result.

 

[T. Valko]

You're welcome!

--
Biff
Microsoft Excel MVP

Thank you so much!

I understand now!