+ Reply to Thread
Results 1 to 5 of 5

Ignore blanks in sumproduct formula

  1. #1
    Registered User
    Join Date
    02-02-2012
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    10

    Ignore blanks in sumproduct formula

    I've found a simple formula to sum the total number of unique values in a column of data (invoice numbers), but have run into a spot of bother when there are blank cells at ANY point in the column. If all cells in the referenced range contain a value, it works perfectly. Does anyone know how i can modify the formula to ignore all blank cells? I've tried it as an array formula, but still no luck. Any ideas?

    =SUMPRODUCT(1/COUNTIF(CB1:CB50,CB1:CB50))

    Cheers!

  2. #2
    Forum Expert OnErrorGoto0's Avatar
    Join Date
    12-30-2011
    Location
    I DO NOT POST HERE ANYMORE
    MS-Off Ver
    I DO NOT POST HERE ANYMORE
    Posts
    1,655

    Re: Ignore blanks in sumproduct formula

    Array formula
    =SUMPRODUCT(IF(CB1:CB50="",0,1/COUNTIF(CB1:CB50,CB1:CB50&"")))
    Good luck.

  3. #3
    Registered User
    Join Date
    02-02-2012
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: Ignore blanks in sumproduct formula

    Many Thanks,

    It didn't quite work as it returned a decimal (0.3) when the outcome should have been 4. However you suggestion did make me look at the problem a different way and the result is this:

    =IF(CB1:CB50="",0,SUM(SUMPRODUCT(1/COUNTIF(CB1:CB50,CB1:CB50&"")))-1)

    And it works perfectly!!!

    Thanks for your help!

  4. #4
    Registered User
    Join Date
    02-02-2012
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: Ignore blanks in sumproduct formula

    NOTE! for my purposes, the minus 1 works as i will ALWAYS have a blank cell somewhere in the column. If a column is full with NO blanks, the formula will return an innacurate value (1 less than the true value).

  5. #5
    Forum Expert OnErrorGoto0's Avatar
    Join Date
    12-30-2011
    Location
    I DO NOT POST HERE ANYMORE
    MS-Off Ver
    I DO NOT POST HERE ANYMORE
    Posts
    1,655

    Re: Ignore blanks in sumproduct formula

    I would guess you did not array enter it then.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1