Results 1 to 2 of 2
  1. #1
    Join Date
    May 2002
    Posts
    157

    Unanswered: Excel - count unique value in a text column but do not count blank cells

    Excel - count unique value in a text column but do not count blank cells

    I have a column which has duplicate values and some cells are blank cells.

    The following works as long as I have data entered into every cell:

    =SUM(IF(FREQUENCY(MATCH(I3:I70,I3:I70,0),MATCH(I3: I70,I3:I70,0))>0,1))

    But if I have any blank cells in the data range then I am given a #N/A error message.

    Please advise what I need to do.

    Example - the following show 17 cells (including 3 blank cells).
    If I use the above formula to give me the number of unique data, my answer should be 5 (groups of the same data). Unfortunately the above formula does not work when there are blank cells as I get a #N/A error. What do I need to do the fix the formula?

    Speaks Sept 2011
    Speaks Sept 2011
    Speaks Sept 2011
    Steering group July 2011
    Steering group July 2011



    Steering group August 2011
    Steering group August 2011
    Steering group August 2011
    Steering group September 2011
    Steering group September 2011
    Steering group September 2011
    Steering group September 2011
    Steering group October 2011
    Steering group October 2011


    thanks heaps
    Last edited by Karen Day; 11-07-11 at 07:29.

  2. #2
    Join Date
    May 2002
    Posts
    157

    Count Unique values in a column ignoring blanks

    I found the answer to this question on You TUBE

    Excel Magic Trick # 272: Count Unique Or Duplicate Records - YouTube

    =SUMPRODUCT(('YWLP DATA 2011-2012'!I3:I70<>"")*(1/COUNTIF('YWLP DATA 2011-2012'!I3:I70,'YWLP DATA 2011-2012'!I3:I70&"")))

Posting Permissions

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