+ Reply to Thread
Results 1 to 6 of 6

SUMIF calcualting zero but don't want zero to show

  1. #1
    Registered User
    Join Date
    11-09-2012
    Location
    New York State
    MS-Off Ver
    Excel 2007
    Posts
    9

    Question SUMIF calcualting zero but don't want zero to show

    Good afternoon.

    I am using the SUMIF formula and some of the results are zero (which is fine) but I do not want the zero to show. Is there something more in the formula I can put so that when the results are zero the field is left balnk.

    Thanks
    Szp

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

    Re: SUMIF calcualting zero but don't want zero to show

    It might be simpler to format the result cell to show zero as blank, e.g. custom format as

    0;-0;

    or make the formula

    =IF(SUMIF_formula=0,"",SUMIF_formula)
    Audere est facere

  3. #3
    Forum Expert Moo the Dog's Avatar
    Join Date
    09-28-2012
    Location
    Wisconsin
    MS-Off Ver
    Office 365 (PC & Mac)
    Posts
    1,845

    Re: SUMIF calcualting zero but don't want zero to show

    You can try this:

    =IF(your_sumif_formula=0,"",your_sumif_formula)

    or you could change the setting in Excel to Hide zero values.

    - Moo

  4. #4
    Registered User
    Join Date
    02-07-2013
    Location
    Afton, Virginia, USA
    MS-Off Ver
    Excel 2007 and Excel 2010
    Posts
    2

    Re: SUMIF calcualting zero but don't want zero to show

    I would like to know the same thing, but using SUM. What I am trying to do is sum a column of integers, but I don't want to show a zero in the formula's cell if no numbers have been entered in any of the designated cells. I have created a form and want a user to be able to print the form without entering any data in order to give it to someone to fill out by hand. I therefore do not want a zero showing in the formula's cell where the manual form filler needs to write his SUM.

  5. #5
    Valued Forum Contributor loginjmor's Avatar
    Join Date
    01-31-2013
    Location
    Cedar Rapids, Iowa
    MS-Off Ver
    Excel 2013
    Posts
    1,073

    Re: SUMIF calcualting zero but don't want zero to show

    There are at least a couple ways I can think of:

    You could enclose your sumif function in a if statement that would look like =if(sumif(...whatever your sum criteria are...)=0,"",sumif(...whatever your sum criteria are...))
    That just displays a blank if your sum is zero.

    Another way is to format the cell using custom formatting so zeros don't display. Select one of the cells containing your formula. To make it easy on yourself, I would first set the cell formatting to Accounting (click on the $ sign on the Home tab). Then right click on the cell to bring up the quick menu, select Format Cells, Select the Number tab. Select Custom out of the Category list (it's at the bottom usually). In the window just to the right of the Category window just below the word Type, look to see if you can find this text string: _($* "-"??_). Change the $*"-" to "". Now, zeros will not display, but all other numeric results will. Copy and paste this cell to every location you want that formatting to apply.

    There may be even more ways to do this, but those were the two off the top of my head.

  6. #6
    Registered User
    Join Date
    02-07-2013
    Location
    Afton, Virginia, USA
    MS-Off Ver
    Excel 2007 and Excel 2010
    Posts
    2

    Re: SUMIF calcualting zero but don't want zero to show

    I guess I must be really dumb today or something. The suggestion posted by DaddyLongLegs works perfectly! Thanks!

+ 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