1

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}))
user3733504
  • 71
  • 1
  • 7
  • OR conditions in a SUMIFS criteria need to be hard-coded; you cannot use a cell or range reference or formula to evaluate them. I would humbly suggest a UDF that can perform this sort of dynamic criteria. –  May 16 '18 at 05:33

2 Answers2

1

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 concatenation (& "</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

QHarr
  • 83,427
  • 12
  • 54
  • 101
0

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))
Harun24hr
  • 30,391
  • 4
  • 21
  • 36