Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Unique count formula
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Unique count formula
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. -- Biff Microsoft Excel MVP "Daniel Bonallack" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Unique count formula
Thank you so much!
I understand now! "T. Valko" wrote: 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. -- Biff Microsoft Excel MVP "Daniel Bonallack" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Unique count formula
You're welcome!
-- Biff Microsoft Excel MVP "Daniel Bonallack" wrote in message ... Thank you so much! I understand now! "T. Valko" wrote: 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. -- Biff Microsoft Excel MVP "Daniel Bonallack" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I set up a formula to count only unique items in a column? | Excel Worksheet Functions | |||
Attempting to sort unique/only count first record in each unique g | Excel Discussion (Misc queries) | |||
Count unique text | Excel Worksheet Functions | |||
How do I set up a formula to count only unique items in a column? | Excel Worksheet Functions | |||
How to count unique values? | Excel Worksheet Functions |