Using SUMIFS with Criteria as a List in a Cell Value

Question

I want to store the multiple criteria of a SUMIFS function in a single cell with each individual criteria separated by a comma.

I realize you can do this by having each criteria in a separate cell, but I need to have each criteria in a single cell separated by commas (or some other delimiter).

The closest I've gotten is seen in the screenshot below. Cell E4 is the formula if I hard code the criteria (an account of 500 or 600). However, if I put those criteria in the criteria cell, E2, and reference the E2's value/contents through the CELL function, it returns the value "500","600" as """500"",""600""" because it escapes the double quotes and then interprets the value as a text string rather than a list of texts.

Does anyone know how to make cell E2's value be a list of text/strings so that it can be passed into the SUMIFS criteria parameter?

You'd need to write a UDF to do the conversion. See Excel: pass array argument to formula through cell reference — Ron Rosenfeld
Thanks for the direction, I was hoping there was a way to do it without a UDF as I'd rather not have to write an add-in to make the function available to all users. I've written a function which accomplishes this now. Thanks for the help! — jones-chris

barry houdini barry houdini · Accepted Answer · 2017-12-25T11:59:12

Does it need to be SUMIFS?

If you format E2 as text then input 500,600 you can use this formula for the sum

=SUMPRODUCT(ISNUMBER(SEARCH(","&B2:B8&",",","&E2&","))+0,C2:C8)

See screenshot

Using SUMIFS with Criteria as a List in a Cell Value

1 Answers