# EXCEL SUM with multiple criteria for cells range

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?
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
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

Experts Exchange Solution brought to you by