# Dynamic OR criteria for SUMIFS

I have a formula like below

``````=SUM(SUMIFS('Sheet1'!\$AK:\$AK,'Sheet1'!\$AL:\$AL,"<=0",'Sheet1'!\$N:\$N,C2))
``````

I want the C2 to be a dynamic multiple criteria OR field which might range from 1 to 4 criteria.

If it would have been static the formula would be something like below

`````` =SUM(SUMIFS('Sheet1'!\$AK:\$AK,'Sheet1'!\$AL:\$AL,"<=0",'Sheet1'!\$N:\$N,{"262","261","200"}))
``````

How do I do it ? I can't get it to work with {"262","261","200"} as value in C2.

The below doesn't work either after having different values in C2,C3,C4

``````=SUM(SUMIFS('Sheet1'!\$AK:\$AK,'Sheet1'!\$AL:\$AL,"<=0",'Sheet1'!\$N:\$N,{C2,C3,C4}))
``````

Try the following formula. `SUMIFS()` supports multiple criteria so you can user `C2`, `C3`, `C4` as criteria.

``````=SUM(SUMIFS(Sheet1!\$AK:\$AK,Sheet1!\$AL:\$AL,"<=0",Sheet1!\$N:\$N,C2,Sheet1!\$N:\$N,C3,Sheet1!\$N:\$N,C4))
``````

Try the following:

``````=SUM(SUMIFS(Sheet1!\$AK:\$AK,Sheet1!\$AL:\$AL,"<=0",Sheet1!\$N:\$N,FILTERXML("<t><s>" & SUBSTITUTE(C2, ",", "</s><s>") & "</s></t>", "//s")))
``````

Credit to Vafā Sarmast for splitting to array technique.

It seems, from using evaluate formula, that the numbers end up being enclosed in `<s>` tags, which are then used via xpath of `//s`, to return all matching items as a list i.e. the numbers as an array. To insert the tags substitute is used on the existing delimiter along with concantation (`& "</s></t>"`). At least, that is my understanding.

Enter with Ctrl + Shift + Enter as array formula.

Values go in as comma separated in C2

Info:

FILTERXML