Excel - Sumifs referencing a cell with an array

Question

I am trying to write a sumifs formula which contains an array in the criteria but the array is in another cell which the sumifs formula will be referencing.

For example, I want to achieve this result, where I am summing column B if column A equals 5003 and 5009:

=SUM(SUMIFS(B:B,A:A,{"5003","5009"}))

However, instead of writing out "{"5003","5009"}" in the formula, I want this part to be referencing another cell which contains this array. For example, I want to do something like:

=SUM(SUMIFS(B:B,A:A,D1))

Where D1 equals:

D1="{"5003","5009"}"

How can I achieve this?

D1 does not hold an array it holds a string that looks like an array. You can split the values into individual cells then do: =SUM(SUMIFS(B:B,A:A,D1:D2)) Otherwise you will need to parse the string to create the array. — Scott Craner

Harun24HR Harun24HR · Accepted Answer · 2021-08-19T02:12:21

In D1 if your criteria's are comma separated then you can use FILTERXML() to build an array for SUMIFS() criteria. Try below

=SUM(SUMIFS(B:B,A:A,FILTERXML("<t><s>"&SUBSTITUTE($D$1,",","</s><s>")&"</s></t>","//s")))

If you data exactly looks like "{"5003","5009"}" in D1 cell then try below formula.

=SUM(SUMIFS(B:B,A:A,FILTERXML("<t><s>"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($D$1,CHAR(34),""),"{",""),"}",""),",","</s><s>")&"</s></t>","//s")))

Excel - Sumifs referencing a cell with an array

1 Answers