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}))
2 answers

Try the following formula.
SUMIFS()
supports multiple criteria so you can userC2
,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: