Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting Unique Values
"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. They are essentially the same formula. You can simplify it by removing the ^ 2 and testing for blan k =SUM((A1:A15<"")/IF(NOT(COUNTIF(A1:A15,A1:A15)),1,COUNTIF(A1:A15,A1 :A15))) which starts to look more like the SP version. |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting Unique Values
I got the same answer with
=SUMPRODUCT(--(FREQUENCY(A2:A10,A2:A10)0)) as the original formula. If the data was text and I wanted to use Frequency, I think you would have to use the formula you posted. Frequency doesn't like text, but it ignores blanks - hence the IF(Len(..), Match(..), "") combination. If dealing with a contiguous range, however, using Sumproduct/Countif should work w/both numbers and text (and you can include or exclude blanks) - so I wouldn't use Frequency. I think Frequency would be useful for dealing w/non-contigous cells (containing numeric data - I think it was Harlan I saw use it for that purpose). From testing a little, it seems Frequency ignores blanks. If data is numeric and you want to evaluate D3:E5 and G7:H9, then =SUMPRODUCT(--(FREQUENCY((D3:E5,G7:H9),(D3:E5,G7:H9))0)) Not sure about if the cells are non-contiguous and the data is text. Frequency doesn't like text and Len/Match don't seem to work w/two dimensional arrays, much less non-contiguous cells - just based on my observations, which doesn't mean someone can't do it. "Epinn" wrote: 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 |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting Unique Values
Bob,
Thank you for asking the question. I have learned a lot. I hope you have got what you wanted. You asked for a formula and I think you have a choice of two formulae now (COUNTIF and FREQUENCY). Please feel free to post any followup questions. Bob P., Thank you for the formula. It looks smarter now. I never like ^2. The last paragraph of your standard reply is precious. I know the purpose of &"" but I don't think there is really an explanation for its formation. I'll take it as part of the syntax. JMB, Thank you for taking the time to experiment with FREQUENCY. I appreciate it very much. I think Frequency would be useful for dealing w/non-contiguous cells (containing numeric data ...........). If data is numeric and you want to evaluate D3:E5 and G7:H9, then =SUMPRODUCT(--(FREQUENCY((D3:E5,G7:H9),(D3:E5,G7:H9))0)) This information is invaluable. I never thought of "non-contiguous" arrays. The formula on my record looks like this {=SUM(IF(FREQUENCY(A2:A10,A2:A10)0,1))} and your use of SUMPRODUCT makes it look smarter. Bob's question is on dates which are numeric. So, I think the following formula which takes care of blanks can serve his purpose. =SUMPRODUCT(--(FREQUENCY(A1:A15,A1:A15)0)) I tested it and it gave the same results as =SUMPRODUCT((A1:A15<"")/COUNTIF(A1:A15,A1:A15&"")) Bob can pick and choose now. Thank you all for a wonderful lesson. Epinn "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 |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting Unique Values
The ^2 is necessary in the first formula because COUNTIF(A1:A15,A1:A15)
returns a count a of each instance of each item. (A1:A15<"") just returns a TRUE/FALSE for each instance. Both are divided by the count of the instances, but as the first is already a count of the instances, it has to be squared so as to get the correct fractional components. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Epinn" wrote in message ... Bob, Thank you for asking the question. I have learned a lot. I hope you have got what you wanted. You asked for a formula and I think you have a choice of two formulae now (COUNTIF and FREQUENCY). Please feel free to post any followup questions. Bob P., Thank you for the formula. It looks smarter now. I never like ^2. The last paragraph of your standard reply is precious. I know the purpose of &"" but I don't think there is really an explanation for its formation. I'll take it as part of the syntax. JMB, Thank you for taking the time to experiment with FREQUENCY. I appreciate it very much. I think Frequency would be useful for dealing w/non-contiguous cells (containing numeric data ...........). If data is numeric and you want to evaluate D3:E5 and G7:H9, then =SUMPRODUCT(--(FREQUENCY((D3:E5,G7:H9),(D3:E5,G7:H9))0)) This information is invaluable. I never thought of "non-contiguous" arrays. The formula on my record looks like this {=SUM(IF(FREQUENCY(A2:A10,A2:A10)0,1))} and your use of SUMPRODUCT makes it look smarter. Bob's question is on dates which are numeric. So, I think the following formula which takes care of blanks can serve his purpose. =SUMPRODUCT(--(FREQUENCY(A1:A15,A1:A15)0)) I tested it and it gave the same results as =SUMPRODUCT((A1:A15<"")/COUNTIF(A1:A15,A1:A15&"")) Bob can pick and choose now. Thank you all for a wonderful lesson. Epinn |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting Unique Values
The formulae discussed in this thread should serve Bob's needs.
Experts, please confirm that the formulae are only good as long as there are no trailing spaces. e.g. AA111 and AA111__ ( __ are trailing spaces) will be counted twice. If we are really picky, we'll do LEN( ), SUBSTITUTE ( ) etc., right? Epinn "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 |
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting Unique Values
This is how I would remove trailing spaces.
=SUBSTITUTE(A1,CHAR(32),REPT("",255)) Please correct me if I am wrong. Thanks. Epinn "Epinn" wrote in message ... The formulae discussed in this thread should serve Bob's needs. Experts, please confirm that the formulae are only good as long as there are no trailing spaces. e.g. AA111 and AA111__ ( __ are trailing spaces) will be counted twice. If we are really picky, we'll do LEN( ), SUBSTITUTE ( ) etc., right? Epinn "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 |
#17
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting Unique Values
Epinn,
The following formula entered as an array i.e. Ctrl+Shift+Enter will correctly report 4 unique dates. =SUM(1/COUNTIF(A1:A9,A1:A9)) HTH Rob "Epinn" wrote in message ... The formulae discussed in this thread should serve Bob's needs. Experts, please confirm that the formulae are only good as long as there are no trailing spaces. e.g. AA111 and AA111__ ( __ are trailing spaces) will be counted twice. If we are really picky, we'll do LEN( ), SUBSTITUTE ( ) etc., right? Epinn "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 |
#18
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting Unique Values
Try test your formula with a range that contains a blank and you'll know why the experts and I won't recommend it. Also, I am a fan of SUMPRODUCT, so I'll use SUMPRODUCT instead of SUM and CSE if there are no blanks.
Thanks for dropping by and sharing. Epinn "Rob" wrote in message ... Epinn, The following formula entered as an array i.e. Ctrl+Shift+Enter will correctly report 4 unique dates. =SUM(1/COUNTIF(A1:A9,A1:A9)) HTH Rob "Epinn" wrote in message ... The formulae discussed in this thread should serve Bob's needs. Experts, please confirm that the formulae are only good as long as there are no trailing spaces. e.g. AA111 and AA111__ ( __ are trailing spaces) will be counted twice. If we are really picky, we'll do LEN( ), SUBSTITUTE ( ) etc., right? Epinn "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 |
#19
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting Unique Values
Absolutely no difference to the much simpler
=SUBSTITUTE(A1," ","") -- Regards Roger Govier "Epinn" wrote in message ... This is how I would remove trailing spaces. =SUBSTITUTE(A1,CHAR(32),REPT("",255)) Please correct me if I am wrong. Thanks. Epinn "Epinn" wrote in message ... The formulae discussed in this thread should serve Bob's needs. Experts, please confirm that the formulae are only good as long as there are no trailing spaces. e.g. AA111 and AA111__ ( __ are trailing spaces) will be counted twice. If we are really picky, we'll do LEN( ), SUBSTITUTE ( ) etc., right? Epinn "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 |
#20
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting Unique Values
Don't understand this comment Epinn:
(emphasis mine) <<<"so I'll use SUMPRODUCT instead of SUM and CSE *IF THERE ARE NO BLANKS*." The Sumproduct() formula Biff posted *will work* with blanks! -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Epinn" wrote in message ... Try test your formula with a range that contains a blank and you'll know why the experts and I won't recommend it. Also, I am a fan of SUMPRODUCT, so I'll use SUMPRODUCT instead of SUM and CSE if there are no blanks. Thanks for dropping by and sharing. Epinn "Rob" wrote in message ... Epinn, The following formula entered as an array i.e. Ctrl+Shift+Enter will correctly report 4 unique dates. =SUM(1/COUNTIF(A1:A9,A1:A9)) HTH Rob "Epinn" wrote in message ... The formulae discussed in this thread should serve Bob's needs. Experts, please confirm that the formulae are only good as long as there are no trailing spaces. e.g. AA111 and AA111__ ( __ are trailing spaces) will be counted twice. If we are really picky, we'll do LEN( ), SUBSTITUTE ( ) etc., right? Epinn "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 |
#21
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting Unique Values
Roger,
Ah, that's why you are the expert. (I laughed at my formula after I have seen yours.) I am glad I posted and you responded. Lately, I have been learning about CHAR and I am attracted to it while I have forgotten the simple things in life. <g I thought I needed REPT to catch all. I read an example of *adding* trailing spaces and REPT is used. Is it true that we need REPT when adding trailing spaces? Thank you for helping me all the time. By the way, have you read my post about using PivotTable to check for duplicates? Epinn "Roger Govier" wrote in message ... Absolutely no difference to the much simpler =SUBSTITUTE(A1," ","") -- Regards Roger Govier "Epinn" wrote in message ... This is how I would remove trailing spaces. =SUBSTITUTE(A1,CHAR(32),REPT("",255)) Please correct me if I am wrong. Thanks. Epinn "Epinn" wrote in message ... The formulae discussed in this thread should serve Bob's needs. Experts, please confirm that the formulae are only good as long as there are no trailing spaces. e.g. AA111 and AA111__ ( __ are trailing spaces) will be counted twice. If we are really picky, we'll do LEN( ), SUBSTITUTE ( ) etc., right? Epinn "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 |
#22
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting Unique Values
RD,
Thank you for asking. Sometimes I thought people could read my mind. <g What I was trying to say is this:- Rob's formula =SUM(1/COUNTIF(A1:A9,A1:A9)) works fine when there are no blanks in the array. However, since I am a fan of SUMPRODUCT, I'll use =SUMPRODUCT(1/COUNTIF(A1:A9,A1:A9)) instead of =SUM(1/COUNTIF(A1:A9,A1:A9)) + CSE even if there are no blanks in the array. The Sumproduct() formula Biff posted *will work* with blanks! << Totally agree. I am very much aware of what each formula in this thread does as I have spent hours experimenting. Biff's formula is what I live by when it comes to counting unique values. It just doesn't take care of blanks but much more. As JMB highlighted, unlike the FREQUENCY formula which takes care of blanks and numbers, Biff's formula will take care of *text*, numbers and blanks. Sounds like a one-fits-all formula. I should memorize it by heart. <g Now my turn to ask you. What do you mean by "(emphasis mine)?" By the way, is Ragdyer your last name or first and last name combined i.e. Rag = first name and Dyer = last name? I won't ask you about your gender. <g Epinn "Ragdyer" wrote in message ... Don't understand this comment Epinn: (emphasis mine) <<<"so I'll use SUMPRODUCT instead of SUM and CSE *IF THERE ARE NO BLANKS*." The Sumproduct() formula Biff posted *will work* with blanks! -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Epinn" wrote in message ... Try test your formula with a range that contains a blank and you'll know why the experts and I won't recommend it. Also, I am a fan of SUMPRODUCT, so I'll use SUMPRODUCT instead of SUM and CSE if there are no blanks. Thanks for dropping by and sharing. Epinn "Rob" wrote in message ... Epinn, The following formula entered as an array i.e. Ctrl+Shift+Enter will correctly report 4 unique dates. =SUM(1/COUNTIF(A1:A9,A1:A9)) HTH Rob "Epinn" wrote in message ... The formulae discussed in this thread should serve Bob's needs. Experts, please confirm that the formulae are only good as long as there are no trailing spaces. e.g. AA111 and AA111__ ( __ are trailing spaces) will be counted twice. If we are really picky, we'll do LEN( ), SUBSTITUTE ( ) etc., right? Epinn "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 |
#23
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting Unique Values
"Epinn" wrote in message ... RD, By the way, is Ragdyer your last name or first and last name combined i.e. Rag = first name and Dyer = last name? I won't ask you about your gender. <g http://tinyurl.com/y2kzhm |
#24
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting Unique Values
When quoting a statement, the perception is that the quote is the *exact*
depiction of the original. However, to accentuate and bring attention to a portion of the quote, the quote may be *modified*, and is therefore no longer an *exact* depiction of the original (you didn't capitalize and wrap those words in asterisks). It is proper decorum to bring attention to the modifications made to the quote by the person using the quote. "emphasis mine" is describing that *I* changed the quote by emphasizing a portion of it. Ragdyer is my "handle", going back to the old days of CB radio. I carried it forward to the "new" internet. It just describes my profession of being in the textile dyeing trade. By coincidence, RD does not stand for RagDyer, but my actual name of Rick Dormack. I just continued to use my handle exclusively, when I was told by some here that I *shouldn't*!<bg You can surmise by my name that I'm male. Now, why all the secrecy about your identity? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Epinn" wrote in message ... RD, Thank you for asking. Sometimes I thought people could read my mind. <g What I was trying to say is this:- Rob's formula =SUM(1/COUNTIF(A1:A9,A1:A9)) works fine when there are no blanks in the array. However, since I am a fan of SUMPRODUCT, I'll use =SUMPRODUCT(1/COUNTIF(A1:A9,A1:A9)) instead of =SUM(1/COUNTIF(A1:A9,A1:A9)) + CSE even if there are no blanks in the array. The Sumproduct() formula Biff posted *will work* with blanks! << Totally agree. I am very much aware of what each formula in this thread does as I have spent hours experimenting. Biff's formula is what I live by when it comes to counting unique values. It just doesn't take care of blanks but much more. As JMB highlighted, unlike the FREQUENCY formula which takes care of blanks and numbers, Biff's formula will take care of *text*, numbers and blanks. Sounds like a one-fits-all formula. I should memorize it by heart. <g Now my turn to ask you. What do you mean by "(emphasis mine)?" By the way, is Ragdyer your last name or first and last name combined i.e. Rag = first name and Dyer = last name? I won't ask you about your gender. <g Epinn "Ragdyer" wrote in message ... Don't understand this comment Epinn: (emphasis mine) <<<"so I'll use SUMPRODUCT instead of SUM and CSE *IF THERE ARE NO BLANKS*." The Sumproduct() formula Biff posted *will work* with blanks! -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Epinn" wrote in message ... Try test your formula with a range that contains a blank and you'll know why the experts and I won't recommend it. Also, I am a fan of SUMPRODUCT, so I'll use SUMPRODUCT instead of SUM and CSE if there are no blanks. Thanks for dropping by and sharing. Epinn "Rob" wrote in message ... Epinn, The following formula entered as an array i.e. Ctrl+Shift+Enter will correctly report 4 unique dates. =SUM(1/COUNTIF(A1:A9,A1:A9)) HTH Rob "Epinn" wrote in message ... The formulae discussed in this thread should serve Bob's needs. Experts, please confirm that the formulae are only good as long as there are no trailing spaces. e.g. AA111 and AA111__ ( __ are trailing spaces) will be counted twice. If we are really picky, we'll do LEN( ), SUBSTITUTE ( ) etc., right? Epinn "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 |
#25
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting Unique Values
You've got a good memory Mr. Phillips!<bg
-- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Bob Phillips" wrote in message ... "Epinn" wrote in message ... RD, By the way, is Ragdyer your last name or first and last name combined i.e. Rag = first name and Dyer = last name? I won't ask you about your gender. <g http://tinyurl.com/y2kzhm |
#26
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting Unique Values
Ragdyer is my "handle"......<<
You sound hitech by using the word "handle." When I see the word "handle," I think TINYURL ...... <g So, you have a story to tell about your user ID as well. Dyer is a very legitimate surname. If I don't hear a "no," I'll call you Rick from now on. Actually, RD has less keystrokes. Well, let my mood decide. Epinn "Ragdyer" wrote in message ... When quoting a statement, the perception is that the quote is the *exact* depiction of the original. However, to accentuate and bring attention to a portion of the quote, the quote may be *modified*, and is therefore no longer an *exact* depiction of the original (you didn't capitalize and wrap those words in asterisks). It is proper decorum to bring attention to the modifications made to the quote by the person using the quote. "emphasis mine" is describing that *I* changed the quote by emphasizing a portion of it. Ragdyer is my "handle", going back to the old days of CB radio. I carried it forward to the "new" internet. It just describes my profession of being in the textile dyeing trade. By coincidence, RD does not stand for RagDyer, but my actual name of Rick Dormack. I just continued to use my handle exclusively, when I was told by some here that I *shouldn't*!<bg You can surmise by my name that I'm male. Now, why all the secrecy about your identity? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Epinn" wrote in message ... RD, Thank you for asking. Sometimes I thought people could read my mind. <g What I was trying to say is this:- Rob's formula =SUM(1/COUNTIF(A1:A9,A1:A9)) works fine when there are no blanks in the array. However, since I am a fan of SUMPRODUCT, I'll use =SUMPRODUCT(1/COUNTIF(A1:A9,A1:A9)) instead of =SUM(1/COUNTIF(A1:A9,A1:A9)) + CSE even if there are no blanks in the array. The Sumproduct() formula Biff posted *will work* with blanks! << Totally agree. I am very much aware of what each formula in this thread does as I have spent hours experimenting. Biff's formula is what I live by when it comes to counting unique values. It just doesn't take care of blanks but much more. As JMB highlighted, unlike the FREQUENCY formula which takes care of blanks and numbers, Biff's formula will take care of *text*, numbers and blanks. Sounds like a one-fits-all formula. I should memorize it by heart. <g Now my turn to ask you. What do you mean by "(emphasis mine)?" By the way, is Ragdyer your last name or first and last name combined i.e. Rag = first name and Dyer = last name? I won't ask you about your gender. <g Epinn "Ragdyer" wrote in message ... Don't understand this comment Epinn: (emphasis mine) <<<"so I'll use SUMPRODUCT instead of SUM and CSE *IF THERE ARE NO BLANKS*." The Sumproduct() formula Biff posted *will work* with blanks! -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Epinn" wrote in message ... Try test your formula with a range that contains a blank and you'll know why the experts and I won't recommend it. Also, I am a fan of SUMPRODUCT, so I'll use SUMPRODUCT instead of SUM and CSE if there are no blanks. Thanks for dropping by and sharing. Epinn "Rob" wrote in message ... Epinn, The following formula entered as an array i.e. Ctrl+Shift+Enter will correctly report 4 unique dates. =SUM(1/COUNTIF(A1:A9,A1:A9)) HTH Rob "Epinn" wrote in message ... The formulae discussed in this thread should serve Bob's needs. Experts, please confirm that the formulae are only good as long as there are no trailing spaces. e.g. AA111 and AA111__ ( __ are trailing spaces) will be counted twice. If we are really picky, we'll do LEN( ), SUBSTITUTE ( ) etc., right? Epinn "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 |
#27
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting Unique Values
You very nonchalantly bypassed *my* question though!<g
-- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Epinn" wrote in message ... Ragdyer is my "handle"......<< You sound hitech by using the word "handle." When I see the word "handle," I think TINYURL ...... <g So, you have a story to tell about your user ID as well. Dyer is a very legitimate surname. If I don't hear a "no," I'll call you Rick from now on. Actually, RD has less keystrokes. Well, let my mood decide. Epinn "Ragdyer" wrote in message ... When quoting a statement, the perception is that the quote is the *exact* depiction of the original. However, to accentuate and bring attention to a portion of the quote, the quote may be *modified*, and is therefore no longer an *exact* depiction of the original (you didn't capitalize and wrap those words in asterisks). It is proper decorum to bring attention to the modifications made to the quote by the person using the quote. "emphasis mine" is describing that *I* changed the quote by emphasizing a portion of it. Ragdyer is my "handle", going back to the old days of CB radio. I carried it forward to the "new" internet. It just describes my profession of being in the textile dyeing trade. By coincidence, RD does not stand for RagDyer, but my actual name of Rick Dormack. I just continued to use my handle exclusively, when I was told by some here that I *shouldn't*!<bg You can surmise by my name that I'm male. Now, why all the secrecy about your identity? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Epinn" wrote in message ... RD, Thank you for asking. Sometimes I thought people could read my mind. <g What I was trying to say is this:- Rob's formula =SUM(1/COUNTIF(A1:A9,A1:A9)) works fine when there are no blanks in the array. However, since I am a fan of SUMPRODUCT, I'll use =SUMPRODUCT(1/COUNTIF(A1:A9,A1:A9)) instead of =SUM(1/COUNTIF(A1:A9,A1:A9)) + CSE even if there are no blanks in the array. The Sumproduct() formula Biff posted *will work* with blanks! << Totally agree. I am very much aware of what each formula in this thread does as I have spent hours experimenting. Biff's formula is what I live by when it comes to counting unique values. It just doesn't take care of blanks but much more. As JMB highlighted, unlike the FREQUENCY formula which takes care of blanks and numbers, Biff's formula will take care of *text*, numbers and blanks. Sounds like a one-fits-all formula. I should memorize it by heart. <g Now my turn to ask you. What do you mean by "(emphasis mine)?" By the way, is Ragdyer your last name or first and last name combined i.e. Rag = first name and Dyer = last name? I won't ask you about your gender. <g Epinn "Ragdyer" wrote in message ... Don't understand this comment Epinn: (emphasis mine) <<<"so I'll use SUMPRODUCT instead of SUM and CSE *IF THERE ARE NO BLANKS*." The Sumproduct() formula Biff posted *will work* with blanks! -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Epinn" wrote in message ... Try test your formula with a range that contains a blank and you'll know why the experts and I won't recommend it. Also, I am a fan of SUMPRODUCT, so I'll use SUMPRODUCT instead of SUM and CSE if there are no blanks. Thanks for dropping by and sharing. Epinn "Rob" wrote in message ... Epinn, The following formula entered as an array i.e. Ctrl+Shift+Enter will correctly report 4 unique dates. =SUM(1/COUNTIF(A1:A9,A1:A9)) HTH Rob "Epinn" wrote in message ... The formulae discussed in this thread should serve Bob's needs. Experts, please confirm that the formulae are only good as long as there are no trailing spaces. e.g. AA111 and AA111__ ( __ are trailing spaces) will be counted twice. If we are really picky, we'll do LEN( ), SUBSTITUTE ( ) etc., right? Epinn "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 |
#28
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting Unique Values
I think I'll use TRIM( ) if you have no objection.
Epinn "Roger Govier" wrote in message ... Absolutely no difference to the much simpler =SUBSTITUTE(A1," ","") -- Regards Roger Govier "Epinn" wrote in message ... This is how I would remove trailing spaces. =SUBSTITUTE(A1,CHAR(32),REPT("",255)) Please correct me if I am wrong. Thanks. Epinn "Epinn" wrote in message ... The formulae discussed in this thread should serve Bob's needs. Experts, please confirm that the formulae are only good as long as there are no trailing spaces. e.g. AA111 and AA111__ ( __ are trailing spaces) will be counted twice. If we are really picky, we'll do LEN( ), SUBSTITUTE ( ) etc., right? Epinn "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 |
#29
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting Unique Values
That is because I too was fascinated by your handle, so I looked it up, and
I remember that it was in response to Stephen :-). Bob "Ragdyer" wrote in message ... You've got a good memory Mr. Phillips!<bg -- Regards, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "Bob Phillips" wrote in message ... "Epinn" wrote in message ... RD, By the way, is Ragdyer your last name or first and last name combined i.e. Rag = first name and Dyer = last name? I won't ask you about your gender. <g http://tinyurl.com/y2kzhm |
#30
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting Unique Values
"Ragdyer" wrote in message ... I just continued to use my handle exclusively, when I was told by some here that I *shouldn't*!<bg Whoever suggested that? I remember when 'onedaywhen' was outed as Jamie Collins, I always thought it was a shame that he dropped the handle, it gave a certain panache to his posts. I hope you never drop RagDyer, it adds diversity to the group. |
#31
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting Unique Values
Bob Phillips wrote:
I remember when 'onedaywhen' was outed as Jamie Collins, I always thought it was a shame that he dropped the handle, it gave a certain panache to his posts. Google doesn't seem to want me to drop the handle either, Bob. I've long since removed it as a nickname from my profile, however it still gets applied unless I've subscribed to a group (whatever that means). So about half my replies are attributed to onedaywhen (try a google search of recent posts). FWIW I reverted to using my given name due to negative feedback: one person thought it was a threat, another suggested it was a pseudonym to hide behind. Neither was the case. A so-called 'real' name seems to be preferred. Jamie. -- |
#32
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting Unique Values
Jamie,
Good to hear from you again. I think I remember you telling me that once before, odd isn't it? That negative feedback you refer to was presumably the same sort of rubbish that Ragdyer was getting. He's more stubborn than you though <G. Anyway, I hope that you are well and all is good in ADO/SQL-land. "Jamie Collins" wrote in message oups.com... Bob Phillips wrote: I remember when 'onedaywhen' was outed as Jamie Collins, I always thought it was a shame that he dropped the handle, it gave a certain panache to his posts. Google doesn't seem to want me to drop the handle either, Bob. I've long since removed it as a nickname from my profile, however it still gets applied unless I've subscribed to a group (whatever that means). So about half my replies are attributed to onedaywhen (try a google search of recent posts). FWIW I reverted to using my given name due to negative feedback: one person thought it was a threat, another suggested it was a pseudonym to hide behind. Neither was the case. A so-called 'real' name seems to be preferred. Jamie. -- |
#33
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting Unique Values
It is amazing that Jamie could hear Bob mention his/her name and showed up "instantly."
RD, I like handles and you know why. Is it true that whoever knows your real name is in your inner circle? <bg Ricky D. wrote: "Now, why all the secrecy about your identity?...... ............................You very nonchalantly bypassed *my* question though!<g" Response: Come visit me at MARS-o-Soft 1010111000111010100010101010^9 }......}...... Epinn, call home ......{......{ Got to go. Happy Halloween to all! "Ragdyer" wrote in message ... You very nonchalantly bypassed *my* question though!<g -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Epinn" wrote in message ... Ragdyer is my "handle"......<< You sound hitech by using the word "handle." When I see the word "handle," I think TINYURL ...... <g So, you have a story to tell about your user ID as well. Dyer is a very legitimate surname. If I don't hear a "no," I'll call you Rick from now on. Actually, RD has less keystrokes. Well, let my mood decide. Epinn "Ragdyer" wrote in message ... When quoting a statement, the perception is that the quote is the *exact* depiction of the original. However, to accentuate and bring attention to a portion of the quote, the quote may be *modified*, and is therefore no longer an *exact* depiction of the original (you didn't capitalize and wrap those words in asterisks). It is proper decorum to bring attention to the modifications made to the quote by the person using the quote. "emphasis mine" is describing that *I* changed the quote by emphasizing a portion of it. Ragdyer is my "handle", going back to the old days of CB radio. I carried it forward to the "new" internet. It just describes my profession of being in the textile dyeing trade. By coincidence, RD does not stand for RagDyer, but my actual name of Rick Dormack. I just continued to use my handle exclusively, when I was told by some here that I *shouldn't*!<bg You can surmise by my name that I'm male. Now, why all the secrecy about your identity? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Epinn" wrote in message ... RD, Thank you for asking. Sometimes I thought people could read my mind. <g What I was trying to say is this:- Rob's formula =SUM(1/COUNTIF(A1:A9,A1:A9)) works fine when there are no blanks in the array. However, since I am a fan of SUMPRODUCT, I'll use =SUMPRODUCT(1/COUNTIF(A1:A9,A1:A9)) instead of =SUM(1/COUNTIF(A1:A9,A1:A9)) + CSE even if there are no blanks in the array. The Sumproduct() formula Biff posted *will work* with blanks! << Totally agree. I am very much aware of what each formula in this thread does as I have spent hours experimenting. Biff's formula is what I live by when it comes to counting unique values. It just doesn't take care of blanks but much more. As JMB highlighted, unlike the FREQUENCY formula which takes care of blanks and numbers, Biff's formula will take care of *text*, numbers and blanks. Sounds like a one-fits-all formula. I should memorize it by heart. <g Now my turn to ask you. What do you mean by "(emphasis mine)?" By the way, is Ragdyer your last name or first and last name combined i.e. Rag = first name and Dyer = last name? I won't ask you about your gender. <g Epinn "Ragdyer" wrote in message ... Don't understand this comment Epinn: (emphasis mine) <<<"so I'll use SUMPRODUCT instead of SUM and CSE *IF THERE ARE NO BLANKS*." The Sumproduct() formula Biff posted *will work* with blanks! -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Epinn" wrote in message ... Try test your formula with a range that contains a blank and you'll know why the experts and I won't recommend it. Also, I am a fan of SUMPRODUCT, so I'll use SUMPRODUCT instead of SUM and CSE if there are no blanks. Thanks for dropping by and sharing. Epinn "Rob" wrote in message ... Epinn, The following formula entered as an array i.e. Ctrl+Shift+Enter will correctly report 4 unique dates. =SUM(1/COUNTIF(A1:A9,A1:A9)) HTH Rob "Epinn" wrote in message ... The formulae discussed in this thread should serve Bob's needs. Experts, please confirm that the formulae are only good as long as there are no trailing spaces. e.g. AA111 and AA111__ ( __ are trailing spaces) will be counted twice. If we are really picky, we'll do LEN( ), SUBSTITUTE ( ) etc., right? Epinn "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 |
#34
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting Unique Values
What did Shakespeare say: What's in a name? A rose is a rose ... or something like that. Anyway, don't fret, we'll let diversity live on!<bg And I must configure my O.E. to enable me to pop up like Jamie did, at the mere mention of his name or handle. Regards, RD "Bob Phillips" wrote in message ... "Ragdyer" wrote in message ... I just continued to use my handle exclusively, when I was told by some here that I *shouldn't*!<bg Whoever suggested that? I remember when 'onedaywhen' was outed as Jamie Collins, I always thought it was a shame that he dropped the handle, it gave a certain panache to his posts. I hope you never drop RagDyer, it adds diversity to the group. |
#35
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting Unique Values
If that "Come Visit Me At" is some kind of a test,
I've failed miserably!<g -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Epinn" wrote in message ... It is amazing that Jamie could hear Bob mention his/her name and showed up "instantly." RD, I like handles and you know why. Is it true that whoever knows your real name is in your inner circle? <bg Ricky D. wrote: "Now, why all the secrecy about your identity?...... ............................You very nonchalantly bypassed *my* question though!<g" Response: Come visit me at MARS-o-Soft 1010111000111010100010101010^9 }......}...... Epinn, call home ......{......{ Got to go. Happy Halloween to all! "Ragdyer" wrote in message ... You very nonchalantly bypassed *my* question though!<g -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Epinn" wrote in message ... Ragdyer is my "handle"......<< You sound hitech by using the word "handle." When I see the word "handle," I think TINYURL ...... <g So, you have a story to tell about your user ID as well. Dyer is a very legitimate surname. If I don't hear a "no," I'll call you Rick from now on. Actually, RD has less keystrokes. Well, let my mood decide. Epinn "Ragdyer" wrote in message ... When quoting a statement, the perception is that the quote is the *exact* depiction of the original. However, to accentuate and bring attention to a portion of the quote, the quote may be *modified*, and is therefore no longer an *exact* depiction of the original (you didn't capitalize and wrap those words in asterisks). It is proper decorum to bring attention to the modifications made to the quote by the person using the quote. "emphasis mine" is describing that *I* changed the quote by emphasizing a portion of it. Ragdyer is my "handle", going back to the old days of CB radio. I carried it forward to the "new" internet. It just describes my profession of being in the textile dyeing trade. By coincidence, RD does not stand for RagDyer, but my actual name of Rick Dormack. I just continued to use my handle exclusively, when I was told by some here that I *shouldn't*!<bg You can surmise by my name that I'm male. Now, why all the secrecy about your identity? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Epinn" wrote in message ... RD, Thank you for asking. Sometimes I thought people could read my mind. <g What I was trying to say is this:- Rob's formula =SUM(1/COUNTIF(A1:A9,A1:A9)) works fine when there are no blanks in the array. However, since I am a fan of SUMPRODUCT, I'll use =SUMPRODUCT(1/COUNTIF(A1:A9,A1:A9)) instead of =SUM(1/COUNTIF(A1:A9,A1:A9)) + CSE even if there are no blanks in the array. The Sumproduct() formula Biff posted *will work* with blanks! << Totally agree. I am very much aware of what each formula in this thread does as I have spent hours experimenting. Biff's formula is what I live by when it comes to counting unique values. It just doesn't take care of blanks but much more. As JMB highlighted, unlike the FREQUENCY formula which takes care of blanks and numbers, Biff's formula will take care of *text*, numbers and blanks. Sounds like a one-fits-all formula. I should memorize it by heart. <g Now my turn to ask you. What do you mean by "(emphasis mine)?" By the way, is Ragdyer your last name or first and last name combined i.e. Rag = first name and Dyer = last name? I won't ask you about your gender. <g Epinn "Ragdyer" wrote in message ... Don't understand this comment Epinn: (emphasis mine) <<<"so I'll use SUMPRODUCT instead of SUM and CSE *IF THERE ARE NO BLANKS*." The Sumproduct() formula Biff posted *will work* with blanks! -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Epinn" wrote in message ... Try test your formula with a range that contains a blank and you'll know why the experts and I won't recommend it. Also, I am a fan of SUMPRODUCT, so I'll use SUMPRODUCT instead of SUM and CSE if there are no blanks. Thanks for dropping by and sharing. Epinn "Rob" wrote in message ... Epinn, The following formula entered as an array i.e. Ctrl+Shift+Enter will correctly report 4 unique dates. =SUM(1/COUNTIF(A1:A9,A1:A9)) HTH Rob "Epinn" wrote in message ... The formulae discussed in this thread should serve Bob's needs. Experts, please confirm that the formulae are only good as long as there are no trailing spaces. e.g. AA111 and AA111__ ( __ are trailing spaces) will be counted twice. If we are really picky, we'll do LEN( ), SUBSTITUTE ( ) etc., right? Epinn "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 |
#36
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting Unique Values
I think that whilst Jamie doesn't inhabit anymore, he secretly misses it, so
keeps a watch on :-) Bob "RagDyer" wrote in message ... What did Shakespeare say: What's in a name? A rose is a rose ... or something like that. Anyway, don't fret, we'll let diversity live on!<bg And I must configure my O.E. to enable me to pop up like Jamie did, at the mere mention of his name or handle. Regards, RD "Bob Phillips" wrote in message ... "Ragdyer" wrote in message ... I just continued to use my handle exclusively, when I was told by some here that I *shouldn't*!<bg Whoever suggested that? I remember when 'onedaywhen' was outed as Jamie Collins, I always thought it was a shame that he dropped the handle, it gave a certain panache to his posts. I hope you never drop RagDyer, it adds diversity to the group. |
#37
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting Unique Values
Off Topic - You can ignore.
Bob and RD, Is it true that we have to type Jamie's full name, Jamie Collins, otherwise he won't "hear" us? "What's in a name? That which we call a rose By any other word would smell as sweet." --From Romeo and Juliet (II, ii, 1-2) << I better apologize to those who don't like distraction, before anyone yells at me. From now on, I will include "Off Topic" in the beginning of the post to alert readers. I have seen a MS forum not as focused as this one. ;) I guess it's okay if I don't make this a habit. Epinn "Bob Phillips" wrote in message ... I think that whilst Jamie doesn't inhabit anymore, he secretly misses it, so keeps a watch on :-) Bob "RagDyer" wrote in message ... What did Shakespeare say: What's in a name? A rose is a rose ... or something like that. Anyway, don't fret, we'll let diversity live on!<bg And I must configure my O.E. to enable me to pop up like Jamie did, at the mere mention of his name or handle. Regards, RD "Bob Phillips" wrote in message ... "Ragdyer" wrote in message ... I just continued to use my handle exclusively, when I was told by some here that I *shouldn't*!<bg Whoever suggested that? I remember when 'onedaywhen' was outed as Jamie Collins, I always thought it was a shame that he dropped the handle, it gave a certain panache to his posts. I hope you never drop RagDyer, it adds diversity to the group. |
#38
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting Unique Values
Bob Phillips wrote: I hope that you are well and all is good in ADO/SQL-land. Yes thanks, Bob. Belated congratulations on your MVP award (hey, why doesn't your face show up in photos <g?) Jamie. -- |
#39
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting Unique Values
Epinn wrote: I think that whilst Jamie doesn't inhabit anymore, he secretly misses it, so keeps a watch on :-) Is it true that we have to type Jamie's full name, Jamie Collins, otherwise he won't "hear" us? This is what I 'hear': http://groups.google.com/groups/sear...osoft.public.* Via my RSS reader, I still 'listen' to Daily Dose of Excel (http://www.dicks-blog.com/), otherwise I don't really have time for Excel: I can spend only a finite time on coffee break. Anyhow, I was in danger becoming part of the Excel establishment myself (Groucho and club membership etc). Jamie. -- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting unique values with criteria | Excel Worksheet Functions | |||
list unique values in a column | Excel Worksheet Functions | |||
Populate combo box with unique values only | Excel Discussion (Misc queries) | |||
Counting Values | Excel Worksheet Functions | |||
Counting unique values + frequency | Excel Worksheet Functions |