+ Reply to Thread
Results 1 to 6 of 6

SUMPRODUCT + COUNTIF but exclude blanks

  1. #1
    Registered User
    Join Date
    07-26-2017
    Location
    California
    MS-Off Ver
    2013
    Posts
    3

    SUMPRODUCT + COUNTIF but exclude blanks

    Hello,

    I currently have the formula

    =SUMPRODUCT(1/COUNTIF(A2:A7,A2:A7)) which produces the correct result I want, 3.

    However, I will need to keep the range A2:A11 open as each month, the data within this range will vary.

    Changing the formula to =SUMPRODUCT(1/COUNTIF(A2:A11,A2:A11)) produces a DIV/0! error as cells A8 - A11 are blank.


    Is there something I can change to my formula with range A2:A11 to exclude and not count blanks?

    Thanks in advance!

    Snip.jpg

  2. #2
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: SUMPRODUCT + COUNTIF but exclude blanks

    Try

    =SUMPRODUCT(1/COUNTIF(A2:A11,A2:A11&""))-1

  3. #3
    Registered User
    Join Date
    07-26-2017
    Location
    California
    MS-Off Ver
    2013
    Posts
    3

    Re: SUMPRODUCT + COUNTIF but exclude blanks

    Thank you Jonmo1, this has helped. I feel like adding the "" and -1 is just subtracting 1 from the total.

    - Testing it and if I changed the range to A2:A7, the formula you provided results 2, instead of 3.


    If anyone has a solution to keep the result as 3, that'd be great. Otherwise, it is unlikely that I will fill my entire range (A2:A11) each month, so the formula that Jonmo1 provided will suffice.

    Thanks again!

  4. #4
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: SUMPRODUCT + COUNTIF but exclude blanks

    I subtracted 1 because it will count blank as 1 unique value.
    So Dave Jon and Juck AND Blank = 4

    Can you just ensure the range of the formula always goes at least 1 row past the end of data?

    Maybe
    =SUMPRODUCT(1/COUNTIF(A2:A11,A2:A11&""))-(COUNTBLANK(A2:A11)>0)

  5. #5
    Registered User
    Join Date
    07-26-2017
    Location
    California
    MS-Off Ver
    2013
    Posts
    3

    Re: SUMPRODUCT + COUNTIF but exclude blanks

    That worked! Thank you!

  6. #6
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: SUMPRODUCT + COUNTIF but exclude blanks

    You're welcome.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Exclude Blanks
    By excelnoob927 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-22-2015, 06:53 PM
  2. Formula to exclude negative numbers and blanks
    By Hackett in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-13-2015, 08:18 AM
  3. Dynamic Ranges vs. Formulas that exclude blanks?
    By amartino44 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-15-2013, 02:04 PM
  4. Exclude blanks from formula
    By ads100 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-07-2012, 06:17 PM
  5. [SOLVED] Dynamic Drop Down List - exclude blanks
    By Econocrat in forum Excel General
    Replies: 7
    Last Post: 08-24-2012, 02:10 PM
  6. Replies: 1
    Last Post: 07-19-2012, 05:09 PM
  7. Excel 2007 : MIN, AVE, MAX values but exclude blanks
    By Quagga in forum Excel General
    Replies: 7
    Last Post: 07-15-2010, 02:29 PM

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