Solved

# EXCEL SUM with multiple criteria for cells range

Posted on 2012-03-28
417 Views
Hello!
I need to insert a SUM formula with multiple criteria for a cell range.
Because there are multiple criteria, I cannot use SUMIF
The criteria are for text cells.
I tried this formula: =SUM(IF((K18="Y")+(K18=""),H18,0))
And it works for a single record.
But If I try to use a range of cell, such as:  =SUM(IF((K18:K20="Y")+(K18:K20=""),H18:20,0))
The formula does not work.  I have either wrong data type error or it does not work at all, just displays the formula.

My real formula is more complicated, but I just want to make sure the formula works.
The formula I used above is pretty much meaningless, the idea is to have a sum of cells in the column H if cells in column K are contain values of Y or blank.
I have Excel 2010, I use Ctrl/Shift/Enter before entering the formula.

Could you please suggest how to do it?
0
Question by:pavelmed
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 50

Accepted Solution

barry houdini earned 500 total points
ID: 37779220
Your formula should work if "array entered" (and corrected with added "H"), i.e. use

=SUM(IF((K18:K20="Y")+(K18:K20=""),H18:H20,0))

select formula by pressing F2 key then hold down CTRL and SHIFT keys while pressing ENTER. If done correctly curly braces like { and } will appear around the formula

...or you can use a "non-array" version with SUMPRODUCT like this

=SUMPRODUCT((K18:K20="Y")+(K18:K20=""),H18:H20)

or SUMIF like this

=SUM(SUMIF(K18:K20,{"Y",""},H18:H20))

regards, barry
0

Author Comment

ID: 37779333
Thank you Barry!
I missed "H" only in a code typed in the question description, if the formula it was correct.
It did not work for me but it started to work when I used the order of entering as you described.  But I like the SUMPRODUCT version even more.  I modified it to use my more complex logic, and it still works.
Thank you VERY MUCH!!!
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
###### Suggested Courses
Course of the Month4 days, 23 hours left to enroll