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

  • answered 2018-05-16 05:31 harun24hr

    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))
    

  • answered 2018-05-16 05:47 QHarr

    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

    Formula

    Info:

    FILTERXML