+ Reply to Thread
Results 1 to 6 of 6

Sum Cells Except Blanks

  1. 04-25-2023, 03:58 PM #1
    NEPJohn
    NEPJohn is offline
    Registered User
    Join Date
    04-25-2023
    Location
    Niagara Falls
    MS-Off Ver
    2010
    Posts
    2

    Sum Cells Except Blanks

    I am trying to sum various cells within a worksheet. The HST values for various expenses. They are not all in the same row, column or section.
    I was wondering if there was an easier method outside of the if(and(A1<>"",B7<>"" and so on.

    The cells I need to sum are as follows:

    IF(AND(c12<>"",h12<>"",h25<>"",h33<>""),C12+H12+H25+H33

    And if one of is a blank cell it comes back as an error.

    Any help would be appreciated. A friend suggested sumif with index match but I cannot see how that would work.

    Thank you,

    John
    Reply With QuoteRegister To Reply
  2. 04-25-2023, 04:27 PM #2
    6StringJazzer
    6StringJazzer is offline
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner VA USA
    MS-Off Ver
    MS 365 Family 64-bit 2505
    Posts
    27,559

    Re: Sum Cells Except Blanks

    Dealing with discontiguous ranges is problematic for many functions.

    This really isn't much of an improvement but here is an alternative:
    Formula: copy to clipboard 
    Please Login or Register  to view this content.


    For later Excel versions insert @ to avoid returning an array
    Formula: copy to clipboard 
    Please Login or Register  to view this content.


    sumif with index match
    I cannot see how that would work either.
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]
    Reply With QuoteRegister To Reply
  3. 04-26-2023, 08:22 AM #3
    NEPJohn
    NEPJohn is offline
    Registered User
    Join Date
    04-25-2023
    Location
    Niagara Falls
    MS-Off Ver
    2010
    Posts
    2

    Re: Sum Cells Except Blanks

    Thank you, Jeff.

    It actually hit me on the way home from work yesterday.

    This is what I did and it works perfectly.

    =IF(SUM(IF(C12="",0,C12)+IF(H12="",0,H12)+IF(H25="",0,H25)+IF(H33="",0,H33))=0,"",SUM(IF(C12="",0,C12)+IF(H12="",0,H12)+IF(H25="",0,H25)+IF(H33="",0,H33)))
    Reply With QuoteRegister To Reply
  4. 04-26-2023, 08:41 AM #4
    6StringJazzer
    6StringJazzer is offline
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner VA USA
    MS-Off Ver
    MS 365 Family 64-bit 2505
    Posts
    27,559

    Re: Sum Cells Except Blanks

    Formula: copy to clipboard 
    Please Login or Register  to view this content.

    I thought you wanted an error message if any of the cells was blank.

    Your latest formula is entirely unnecessary. The formula
    Formula: copy to clipboard 
    Please Login or Register  to view this content.

    will do exactly the same thing because SUM ignores blank cells.
    Reply With QuoteRegister To Reply
  5. 04-26-2023, 08:42 AM #5
    Pepe Le Mokko
    Pepe Le Mokko is offline
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2504
    Posts
    13,654

    Re: Sum Cells Except Blanks

    Formula: copy to clipboard 
    Please Login or Register  to view this content.

    What's wrong with
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    ?
    Blank cells always count for 0 and SUM does not see text ?
    How to paste Power Query code ( by Olly)
    Reply With QuoteRegister To Reply
  6. 04-26-2023, 08:47 AM #6
    6StringJazzer
    6StringJazzer is offline
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner VA USA
    MS-Off Ver
    MS 365 Family 64-bit 2505
    Posts
    27,559

    Re: Sum Cells Except Blanks

    Here is an example with other cells (showing your cells would make the post too large)

    Values as displayed
    A
    B
    C
    D
    E
    1
    11
    2
    1
    3
    2
    4
    5
    8

    Underlying formulas
    A
    B
    C
    D
    E
    1
    =SUM(B2,C3,D4,E5)
    2
    1
    3
    2
    4
    5
    8
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. BLANKS (Not Really Blanks) AND Rearrange ignoring blanks
    By shivspatil in forum Excel General
    Replies: 4
    Last Post: 02-02-2018, 08:28 AM
  2. I need to count number of cells with numbers ignoring blanks & #n/a cells
    By drewsta in forum Excel General
    Replies: 6
    Last Post: 12-04-2014, 10:39 AM
  3. I need to count number of cells with numbers ignoring blanks & #n/a cells
    By drewsta in forum Excel General
    Replies: 2
    Last Post: 12-04-2014, 06:23 AM
  4. Data Validation to circle blanks cells between filled cells
    By jamiem4 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-16-2014, 05:58 PM
  5. How to change empty cells to blank cells (need to use skip blanks function)
    By ChristopherH in forum Excel General
    Replies: 4
    Last Post: 03-08-2014, 02:03 PM
  6. How to copy a formula to multiple cells without filling blanks cells too..
    By leftbehind321 in forum Excel General
    Replies: 1
    Last Post: 10-24-2012, 01:21 PM
  7. Count cells without blank cells or space blanks
    By Zaeguzah in forum Excel General
    Replies: 12
    Last Post: 03-04-2010, 07:03 AM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1