Results 1 to 7 of 7
Thread: formula for count (2003)

20051028, 15:53 #1
 Join Date
 Oct 2005
 Location
 Cambridgeshire, USA
 Posts
 109
 Thanks
 0
 Thanked 0 Times in 0 Posts
formula for count (2003)
Hello,
I need to make a count formula that does not include the same numbers. For example, I have numbers in cell A1A200. I would like to count the number of cells that are not the same. I could sort this information, but I would still have to go through all 200 cells to find out if any are the same. Any help would be great.

20051028, 15:56 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: formula for count (2003)
Do you mean the mumber of unique entries? Use this formula:
=SUM(1/COUNTIF(A1:A200,A1:A200))
entered as an array formula, i.e. confirm with Ctrl+Shift+Enter instead of just Enter.

20051028, 16:01 #3
 Join Date
 Jan 2001
 Posts
 3,788
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: formula for count (2003)
If there are no blanks in the range, then use:
<big>=SUM(1/COUNTIF(A1:A200,A1:A200))</big>
If there are blank cells in the range use:
<big>=SUM(IF(COUNTIF(A1:A200,A1:A200)=0,"",1/COUNTIF(A1:A200,A1:A200)))</big>
These are both array formulas and need to entered using Control+Shift+Enter

20051028, 16:23 #4
 Join Date
 Oct 2005
 Location
 Cambridgeshire, USA
 Posts
 109
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: formula for count (2003)
Thanks Hans and Tony. Hans formula work perfect. Another questions, can the formula work for two columns and would compare columns say a1:b1 to a2:b2 and count only the unique values for each row.

20051028, 18:25 #5
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: formula for count (2003)
I'm not sure what you mean by compare in this situation. Could you explain?
For the future: if you have an additional question, it's better to post a new reply than to edit a previous reply. I usually don't look at a reply again after I have read it. By accident, my eye fell on it and I noticed that you had added a question, but I usually don't look at a reply again after I have read it.

20051029, 20:38 #6
 Join Date
 Jan 2002
 Location
 The Hague, Netherlands
 Posts
 283
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: formula for count (2003)
For excluding blanks, the idiom would be:
=SUM(IF(A1:A200<>"",1/COUNTIF(A1:A200,A1:A200)))
followed by control+shift+enter.Microsoft MVP  Excel

20051029, 20:41 #7
 Join Date
 Jan 2002
 Location
 The Hague, Netherlands
 Posts
 283
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: formula for count (2003)
Given:
2,3
2,4
4,2
2,3
2,blank
would the result be 4?Microsoft MVP  Excel