Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    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 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. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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.

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

  4. #4
    2 Star Lounger
    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.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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.

  6. #6
    3 Star Lounger
    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

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

Posting Permissions

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