CODEWITHSUNDEEP
excel-formula
Nick Williams
Nick Williams

Reputation: 23

SUMIFS - No Value Given when using Cell as Criteria

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?

Upvotes: 0

Views: 237

Answers (2)

Nick Williams
Nick Williams

Reputation: 23

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

Upvotes: 1

W_O_L_F
W_O_L_F

Reputation: 1486

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

Upvotes: 0

Related Questions