# Thread: formula for count (2003)

1. ## 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 A1-A200. 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.

2. ## 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.

3. ## 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

4. ## 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.

5. ## 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.

6. ## 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.

7. ## Re: formula for count (2003)

Given:

2,3
2,4
4,2
2,3
2,blank

would the result be 4?

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•