A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Counting Unique Values



 
 
Thread Tools Display Modes
  #11  
Old October 23rd, 2006, 09:48 AM posted to microsoft.public.excel.worksheet.functions
Bob Phillips
external usenet poster
 
Posts: 5,994
Default 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  
Old October 24th, 2006, 02:14 AM posted to microsoft.public.excel.worksheet.functions
JMB
external usenet poster
 
Posts: 1,266
Default 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  
Old October 24th, 2006, 04:33 AM posted to microsoft.public.excel.worksheet.functions
Epinn
external usenet poster
 
Posts: 754
Default 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  
Old October 24th, 2006, 09:21 AM posted to microsoft.public.excel.worksheet.functions
Bob Phillips
external usenet poster
 
Posts: 5,994
Default 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  
Old October 29th, 2006, 07:04 AM posted to microsoft.public.excel.worksheet.functions
Epinn
external usenet poster
 
Posts: 754
Default 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  
Old October 29th, 2006, 10:07 AM posted to microsoft.public.excel.worksheet.functions
Epinn
external usenet poster
 
Posts: 754
Default 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  
Old October 29th, 2006, 10:09 AM posted to microsoft.public.excel.worksheet.functions
Rob
external usenet poster
 
Posts: 63
Default 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  
Old October 29th, 2006, 08:13 PM posted to microsoft.public.excel.worksheet.functions
Epinn
external usenet poster
 
Posts: 754
Default 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  
Old October 29th, 2006, 08:28 PM posted to microsoft.public.excel.worksheet.functions
Roger Govier
external usenet poster
 
Posts: 2,602
Default 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  
Old October 29th, 2006, 08:32 PM posted to microsoft.public.excel.worksheet.functions
RagDyeR
external usenet poster
 
Posts: 3,482
Default 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



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 09:27 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.