+ Reply to Thread
Results 1 to 7 of 7

Summation of values based on multiple criteria & ignoring blanks

  1. #1
    Registered User
    Join Date
    02-03-2012
    Location
    Baltimore, MD
    MS-Off Ver
    Excel 2007
    Posts
    11

    Summation of values based on multiple criteria & ignoring blanks

    I would like to sum a column of values listed in a table based on multiple criteria. I have created cells for the user to input the varying criteria. I'm trying to use a sumproduct formula to evaluate the criteria and calculate the filtered summation. The problem that I have is that not all the criteria will be used. If one of the criteria inputs is left blank, the return value is always "0". How can I setup the formula to ignore the blank cells? Please let me know if there is a more efficient method to complete this calculation. Thanks for your help.

    Here's the formula: =SUMPRODUCT((B4:B19=I3)*(C4:C19=I4)*(D4:D19=I5)*(E4:E19))

  2. #2
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451

    Re: Summation of values based on multiple criteria & ignoring blanks

    Maybe
    =SUMPRODUCT(IF(I3="",1,(B4:B19=I3))*IF(I4="",1,(C4:C19=I4))*IF(I5="",1,(D4:D19=I5))*(E4:E19))

  3. #3
    Valued Forum Contributor ron2k_1's Avatar
    Join Date
    09-30-2009
    Location
    Not the USA
    MS-Off Ver
    Excel 2003, 2007
    Posts
    606

    Re: Summation of values based on multiple criteria & ignoring blanks

    I was trying to figure out what he meant by "Ignoring". But I guess he meant to include corresponding values that have blank cells; here is another take, it may be slower than cutter tho:
    Please Login or Register  to view this content.
    EDIT: Removed the "--", I don't think they're needed.
    Last edited by ron2k_1; 04-17-2012 at 02:25 PM.
    Ron
    Knowledge is knowing that a tomato and pepper are fruits. Wisdom is knowing whether to put these in a fruit salad

    Kindly

    [1] Use code tags. Place "[code]" before the first line of code and "[/code"]" after the last line of code. Exclude quotation marks
    [2] Mark your post [SOLVED] if it has been answered satisfactorily by editing your original post in advanced mode.
    [3] Thank (using the little scale) those that provided useful help; its nice and its very well appreciated

  4. #4
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451

    Re: Summation of values based on multiple criteria & ignoring blanks

    If this works, it is more efficient

    =SUMIFS(E4:E19,B4:B19,IF(I3="","*",I3),C4:C19,IF(I4="","*",I4),D4:D19,IF(I5="","*",I5))

    NOT TESTED

  5. #5
    Registered User
    Join Date
    04-04-2012
    Location
    Bangalore
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Summation of values based on multiple criteria & ignoring blanks

    coreyjo, in case of SUMPRODUCT function, any blank cell is treated as if it contains value 0 (zero), so 0 multiplied by any number is 0. Hence, you have to use it along with IF function as advised by Cutter.

    Regards

  6. #6
    Registered User
    Join Date
    02-03-2012
    Location
    Baltimore, MD
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: Summation of values based on multiple criteria & ignoring blanks

    Thanks for all the suggestions. I'll give those formulas a try and let you know if it resolves my issue. Thanks.

  7. #7
    Registered User
    Join Date
    02-03-2012
    Location
    Baltimore, MD
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: Summation of values based on multiple criteria & ignoring blanks

    This formula resolved my problem: =SUMIFS(E4:E19,B4:B19,IF(I3="","*",I3),C4:C19,IF(I4="","*",I4),D4:D19,IF(I5="","*",I5)). Thanks for the 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