SUMIFS - No Value Given when using Cell as Criteria

Question

The follow Formula give me the correct Value:

=SUM(SUMIFS('POS Data'!$G:$G,'POS Data'!$B:$B,{"5","10","11"}))

However I require part of the criteria to be taken from a cell value. eg

=SUM(SUMIFS('POS Data'!$G:$G,'POS Data'!$B:$B,E1))

E1 cell value = {"5","10","11"}

However the formula gives a 0 value. What am I missing? Why is it not recognizing that E1 is that value?

In the formula `{"5","10","11"}` indicates an array. In the cell `{"5","10","11"}` is a string `"{""5"",""10"",""11""}"` and a string in a formula cannot be converted to an array. You can put one item per cell then refer to the three cells (ie `E1:E3`), which will create the array: `=SUM(SUMIFS('POS Data'!$G:$G,'POS Data'!$B:$B,E1:E3))` — Scott Craner, Nov 04 '21 at 14:36
Otherwise you will need to parse the string to create the array with something like FILTERXML: https://stackoverflow.com/questions/61837696/excel-extract-substrings-from-string-using-filterxml — Scott Craner, Nov 04 '21 at 14:43
@ScottCraner I tried adding the Values that way stated, but still get 0. — Nick Williams, Nov 04 '21 at 15:00
what version do you have? if anything but Office 365 you will need to change the `SUM` to `SUMPRODUCT` or use Ctrl-Shift-Enter to array enter the formula. — Scott Craner, Nov 04 '21 at 15:02
oh and do not use the `"` when they are in the cells. just put `5`,`10`,`11`. — Scott Craner, Nov 04 '21 at 15:04

score 1 · Answer 1 · edited Nov 04 '21 at 15:59

1

The solution used was as follows:

=SUMPRODUCT(SUMIFS('POS Data'!$G:$G,'POS Data'!$B:$B,E1:G1))

E1 = 5
F1 = 10
G1 = 11

That you @scottCraner

edited Nov 04 '21 at 15:59

Scott Craner

answered Nov 04 '21 at 15:36

Nick Williams

score 0 · Answer 2 · edited Nov 04 '21 at 15:50

0

Assuming your import cell with your array is in E1, You have to change it from {"5","10","11"} to 5,10,11 in the cell value

=SUM(SUMIFS(G:G,B:B,TRANSPOSE(FILTERXML("<x><y>"&SUBSTITUTE(E1,",","</y><y>")&"</y></x>","//y"))))

edited Nov 04 '21 at 15:50

Dharman

answered Nov 04 '21 at 15:45

W_O_L_F

2 Answers2