+ Reply to Thread
Results 1 to 3 of 3

Telling excel to ignore blank cells in SUM formulas

  1. #1
    Registered User
    Join Date
    06-27-2008
    Location
    Hull
    Posts
    7

    Telling excel to ignore blank cells in SUM formulas

    Hi There

    Im trying to Sum the total of a number of cells using the following formula:
    =SUM(F57,H57,J57,L57,P57,N57,D57,,R57,V57,T57,X57)

    however some of those cells might be blanks as they are dependant on other data. The probelm im having is that when they are blank i am getting the the sum as 0. i know this isnt the case but dont know how to rectify my code.

  2. #2
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    39,244
    Assuming there are no cells with text, the SUM function is redundant; just

    =F57+H57+J57 ...

  3. #3
    Forum Expert
    Join Date
    12-24-2004
    Location
    Sweden
    Posts
    1,256

    Sum discontined blank formula cells

    For a Discontinued range: (F57,H57,J57,L57,P57,N57,D57,,R57,V57,T57,X57)
    The complicated way is to use: =IF(F57<>"",F57)+IF(H57<>"",H57)+...

    If you can make the range continous by for instance setting a 1 in row 58 for all cells that is to be calculated (and hide the row), then the formulas becomes easier:
    =SUMPRODUCT((F58:X58=1)*(F57:X57<>"")*(F57:X57))

    HTH
    Ola

+ 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