# Summation of values based on multiple criteria & ignoring blanks

1. ## 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))  Register To Reply

2. ## 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))  Register To Reply

3. ## 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.  Register To Reply

4. ## 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  Register To Reply

5. ## 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  Register To Reply

6. ## 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.  Register To Reply

7. ## 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!  Register To Reply