If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Counting Unique Values
I have a column with the following dates:
11/23/05 11/23/05 11/23/05 11/25/05 11/25/05 11/28/05 12/1/05 12/1/05 12/1/05 I want a formula that will tell me how many unique dates there are in the column. Can anyone help? thanks, Bob |
#2
|
|||
|
|||
Counting Unique Values
If memory serves, I believe I've seen this formula suggested by others. It
should ignore blank cells. Change range as needed. =SUMPRODUCT(--(A1:A6""),1/COUNTIF(A1:A6,A1:A6&"")) If there are no empty cells in your data you could shorten it to =SUMPRODUCT(1/COUNTIF(A1:A5,A1:A5)) "bob" wrote: I have a column with the following dates: 11/23/05 11/23/05 11/23/05 11/25/05 11/25/05 11/28/05 12/1/05 12/1/05 12/1/05 I want a formula that will tell me how many unique dates there are in the column. Can anyone help? thanks, Bob |
#3
|
|||
|
|||
Counting Unique Values
Try this:
=SUMPRODUCT(--(A1:A9"")/COUNTIF(A1:A9,A1:A9&"")) Biff "bob" wrote in message ... I have a column with the following dates: 11/23/05 11/23/05 11/23/05 11/25/05 11/25/05 11/28/05 12/1/05 12/1/05 12/1/05 I want a formula that will tell me how many unique dates there are in the column. Can anyone help? thanks, Bob |
#4
|
|||
|
|||
Counting Unique Values
Don't tell me, I know - the 1 is superfluous g.
"Biff" wrote: Try this: =SUMPRODUCT(--(A1:A9"")/COUNTIF(A1:A9,A1:A9&"")) Biff "bob" wrote in message ... I have a column with the following dates: 11/23/05 11/23/05 11/23/05 11/25/05 11/25/05 11/28/05 12/1/05 12/1/05 12/1/05 I want a formula that will tell me how many unique dates there are in the column. Can anyone help? thanks, Bob |
#5
|
|||
|
|||
Counting Unique Values
I only do that when I'm bored and there's nothing to play with!
Biff "JMB" wrote in message ... Don't tell me, I know - the 1 is superfluous g. "Biff" wrote: Try this: =SUMPRODUCT(--(A1:A9"")/COUNTIF(A1:A9,A1:A9&"")) Biff "bob" wrote in message ... I have a column with the following dates: 11/23/05 11/23/05 11/23/05 11/25/05 11/25/05 11/28/05 12/1/05 12/1/05 12/1/05 I want a formula that will tell me how many unique dates there are in the column. Can anyone help? thanks, Bob |
#6
|
|||
|
|||
Counting Unique Values
LOL
We can also question the '--', can't we ? ;-] -- Regards, Luc. "Festina Lente" "JMB" wrote: Don't tell me, I know - the 1 is superfluous g. "Biff" wrote: Try this: =SUMPRODUCT(--(A1:A9"")/COUNTIF(A1:A9,A1:A9&"")) Biff "bob" wrote in message ... I have a column with the following dates: 11/23/05 11/23/05 11/23/05 11/25/05 11/25/05 11/28/05 12/1/05 12/1/05 12/1/05 I want a formula that will tell me how many unique dates there are in the column. Can anyone help? thanks, Bob |
#7
|
|||
|
|||
Counting Unique Values
We can also question the '--', can't we ?
Yes! Touche! Biff "PapaDos" wrote in message ... LOL We can also question the '--', can't we ? ;-] -- Regards, Luc. "Festina Lente" "JMB" wrote: Don't tell me, I know - the 1 is superfluous g. "Biff" wrote: Try this: =SUMPRODUCT(--(A1:A9"")/COUNTIF(A1:A9,A1:A9&"")) Biff "bob" wrote in message ... I have a column with the following dates: 11/23/05 11/23/05 11/23/05 11/25/05 11/25/05 11/28/05 12/1/05 12/1/05 12/1/05 I want a formula that will tell me how many unique dates there are in the column. Can anyone help? thanks, Bob |
#8
|
|||
|
|||
Counting Unique Values
Biff,
That's brilliant! Some time ago, I found the following array formula suggested by an expert. {=SUM(COUNTIF(A1:A15,A1:A15)/IF(NOT(COUNTIF(A1:A15,A1:A15)),1,COUNTIF(A1:A15,A1 :A15))^2)} Like your formula, it takes care of blanks in the array as well. Can you confirm that both your formula and the above formula do exactly the same thing i.e. count unique text and numeric values in a range which may contain blanks. If yes, I'll replace the above with your formula. Regarding the formula =SUMPRODUCT((A1:A15"")/COUNTIF(A1:A15,A1:A15&"")) can you explain the &"" part please? I know if I remove &"" I will get the #DIV/0! error. But I can't tell from "evaluate formula" what &"" is doing? One more question:- I know from my record and JMB's comment that this formula =SUMPRODUCT(1/COUNTIF(A1:A15,A1:A15)) will give an error when there is a blank in the array. When I change it to =SUMPRODUCT(1/COUNTIF(A1:A15,A1:A15&"")) I get a result which counts the blank(s) as well. I guess if I want blanks counted, I can use this formula, right? Another alternative to count unique records is to use Advanced filter, unique records, and COUNTA. This doesn't require any analysis and understanding. Biff, I look forward to your guidance. Thanks! Epinn "Biff" wrote in message ... Try this: =SUMPRODUCT(--(A1:A9"")/COUNTIF(A1:A9,A1:A9&"")) Biff "bob" wrote in message ... I have a column with the following dates: 11/23/05 11/23/05 11/23/05 11/25/05 11/25/05 11/28/05 12/1/05 12/1/05 12/1/05 I want a formula that will tell me how many unique dates there are in the column. Can anyone help? thanks, Bob |
#9
|
|||
|
|||
Counting Unique Values
I only do that when I'm bored and there's nothing to play with!
Do you want to play with the following? {=SUM(IF(FREQUENCY(IF(LEN(A2:A10)0,MATCH(A2:A10,A 2:A10,0),""), IF(LEN(A2:A10)0,MATCH(A2:A10,A2:A10,0),""))0,1)) } If I remember correctly, the above is from Help which usually tells us the "inefficient" way of doing things. Glad to have someone like you, JMB and Luc around. By the way, Luc, you were brilliant the other day switching roles between the LOOKUP table and the data set (LOOKUP values). Bob, I hope you don't mind me showing up. Also, please ignore the formulae I posted because I don't want to confuse you. Epinn "Biff" wrote in message ... I only do that when I'm bored and there's nothing to play with! Biff "JMB" wrote in message ... Don't tell me, I know - the 1 is superfluous g. "Biff" wrote: Try this: =SUMPRODUCT(--(A1:A9"")/COUNTIF(A1:A9,A1:A9&"")) Biff "bob" wrote in message ... I have a column with the following dates: 11/23/05 11/23/05 11/23/05 11/25/05 11/25/05 11/28/05 12/1/05 12/1/05 12/1/05 I want a formula that will tell me how many unique dates there are in the column. Can anyone help? thanks, Bob |
#10
|
|||
|
|||
Counting Unique Values
This is my standard reply to this question
Let's start by defining the range A1:A20 to talk specifics. Bob,John,Bob,Bob,John,John,Bob,Bill,Bill,Max or data in just A1:A10 The basic formula to count unique items is =SUMPRODUCT(1/COUNTIF($A$1:$A$10,$A$1:$A$10)) The COUNTIF($A$1:$A$10,$A$1:$A$10) part of the formula builds an array of the number of occurrences of each item, in this case{4;3;4;4;3;3;4;2;2;1}. As can be seen, each occurrence of the repeated value is counted, so there are four occurrences of Bob in the array. There will always be the same number of occurrences of value as the count of that value, unless two or more items are repeated the same number of times, in which case it will be some multiple of that count. Thus the item that is repeated 4 times has 4 instances of that count, dividing 1 by the count of 4, gives 0.25 4 times. The full array of values is {0.25;0.333333333333333;0.25;0.25;0.33333333333333 3;0.333333333333333;0.25;0 ..5;0.5;1}. The item that repeats 4 times sums to 1. The item that repeats 3 times also sums to 1. It should be clear from this that every value works in the same way and sums to 1. In other words, 1 is returned for every unique item. The sum of these values becomes the count of unique items. As our test range is A1:A20, and some of the items in A1:A20 are blank, extending this formula to A1:A20 would return a #DIV/0! Error. The reason for the error is blank cells in the full range A1:A20. Each blank cell returns a 0 value from the COUNTIF formula, which gives the #DIV/0! Error when divided into 1. The solution to this is to force it to count the empty cells as well, and not return a zero. Adding &"" to the end of the COUNTIF formula forces a count of the blanks. This addition on its own removes the #DIV/0! error, but will cause the blanks to be counted as a unique item. A further addition to the formula resolves this by testing for those blanks. Instead of dividing the array of counts into 1 each time, adding the test creates an array of TRUE/FALSE values to be divided by the equivalent element in the counts array. Each blank will resolve to FALSE in the dividend array, and the count of the blanks in the divisor array. The result of this will be 0, so the blanks do not get counted. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Epinn" wrote in message ... Biff, That's brilliant! Some time ago, I found the following array formula suggested by an expert. {=SUM(COUNTIF(A1:A15,A1:A15)/IF(NOT(COUNTIF(A1:A15,A1:A15)),1,COUNTIF(A1:A15 ,A1:A15))^2)} Like your formula, it takes care of blanks in the array as well. Can you confirm that both your formula and the above formula do exactly the same thing i.e. count unique text and numeric values in a range which may contain blanks. If yes, I'll replace the above with your formula. Regarding the formula =SUMPRODUCT((A1:A15"")/COUNTIF(A1:A15,A1:A15&"")) can you explain the &"" part please? I know if I remove &"" I will get the #DIV/0! error. But I can't tell from "evaluate formula" what &"" is doing? One more question:- I know from my record and JMB's comment that this formula =SUMPRODUCT(1/COUNTIF(A1:A15,A1:A15)) will give an error when there is a blank in the array. When I change it to =SUMPRODUCT(1/COUNTIF(A1:A15,A1:A15&"")) I get a result which counts the blank(s) as well. I guess if I want blanks counted, I can use this formula, right? Another alternative to count unique records is to use Advanced filter, unique records, and COUNTA. This doesn't require any analysis and understanding. Biff, I look forward to your guidance. Thanks! Epinn "Biff" wrote in message ... Try this: =SUMPRODUCT(--(A1:A9"")/COUNTIF(A1:A9,A1:A9&"")) Biff "bob" wrote in message ... I have a column with the following dates: 11/23/05 11/23/05 11/23/05 11/25/05 11/25/05 11/28/05 12/1/05 12/1/05 12/1/05 I want a formula that will tell me how many unique dates there are in the column. Can anyone help? thanks, Bob |
Thread Tools | |
Display Modes | |
|
|