formula for count (2003)

Topic

New Reply

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.

Reply | Quote

Replies

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.

Reply | Quote

If there are no blanks in the range, then use:

=SUM(1/COUNTIF(A1:A200,A1:A200))

If there are blank cells in the range use:

=SUM(IF(COUNTIF(A1:A200,A1:A200)=0,””,1/COUNTIF(A1:A200,A1:A200)))

These are both array formulas and need to entered using Control+Shift+Enter

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

Given:

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

would the result be 4?

Reply | Quote

For excluding blanks, the idiom would be:

=SUM(IF(A1:A200″”,1/COUNTIF(A1:A200,A1:A200)))

followed by control+shift+enter.

Reply | Quote