+ Reply to Thread
Results 1 to 6 of 6

SUMIF Question: SUMIF not blank

  1. #1
    Registered User
    Join Date
    11-18-2009
    Location
    San Francisco
    MS-Off Ver
    Excel 2007
    Posts
    4

    SUMIF Question: SUMIF not blank

    Hi. I have a question about how to do something I'm guessing is very simple, but elusive to me nonetheless. I want to print the sum of the data in a range of cells (say F2:F4) in another cell (say F5), but I only want to do that if there is data in one of the cells that I'm summing.

    I can get it to print the sum, but if they the cells I'm summing are blank, I don't want to print anything in the target cell. I've tried various things, but I always get a 0 to print if the cells are blank. Anyone know what I should do to get it do print nothing in that cell? The 0 is messing up a graph that is tied to the data.

    Any help is MUCH appreciated!

  2. #2
    Valued Forum Contributor khamilton's Avatar
    Join Date
    10-08-2009
    Location
    IL
    MS-Off Ver
    Excel 2007
    Posts
    345

    Re: SUMIF Question: SUMIF not blank

    Try this:
    =if(sum(F2:F4)=0,"",sum(F2:F4))

  3. #3
    Registered User
    Join Date
    11-18-2009
    Location
    San Francisco
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: SUMIF Question: SUMIF not blank

    Yes! Thanks so much.

  4. #4
    Registered User
    Join Date
    11-18-2009
    Location
    San Francisco
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: SUMIF Question: SUMIF not blank

    Except that will leave my target cell blank if I had explicitly set 0 in any of my range cells. In that case, I really do want a 0.

  5. #5
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: SUMIF Question: SUMIF not blank

    Try testing for numbers in the range, i.e.

    =IF(COUNT(F2:F4),SUM(F2:F4),"")

  6. #6
    Registered User
    Join Date
    11-18-2009
    Location
    San Francisco
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: SUMIF Question: SUMIF not blank

    That works. No value is populated unless there are numbers (incl. 0) in at least one of the cells in the range. For some reason, though, my graph still reads sets it's value to 0 if there is anything in that cell -- 0, formula, character, whatever. I'll look into that separately.

    Much thanks for your help.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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