CODEWITHSUNDEEP
excel
PJC83
PJC83

Reputation: 303

Pulling multiple criteria from another cell

A basic table:

Apples 2
Pears 3
Oranges 1
Bananas 3

If I use the code =SUM(SUMIFS(B2:B5,A2:A5,{"Apples","Bananas"})), I get the desired answer of 5.

However, if I enter {"Apples","Bananas"} into another cell (say C6), and change the code to reference the cell =SUM(SUMIFS(B2:B5,A2:A5,C6)) I get 0.

Is there a reason why the same text can't be pulled from another cell? I'm assuming the curly braces are the source of the trauma.

Upvotes: 1

Views: 111

Answers (3)

Error 1004
Error 1004

Reputation: 8220

Formula:

=SUM(SUMIF(A1:A4,A1,B1:B4)+SUMIF(A1:A4,A4,B1:B4))

Results:

enter image description here

Upvotes: 0

RCaetano
RCaetano

Reputation: 673

You can have all the conditions in a single cell (in this case C6):

{=SUM(SUMIFS(B2:B5,A2:A5,TRIM(MID(SUBSTITUTE(C6,",",REPT(" ",255)),255*(ROW(INDIRECT("1:"&1+LEN(C6)-LEN(SUBSTITUTE(C6,",",""))))-1)+1,255))))}

You need to input this as an array formula so press Ctrl+Shift+Enter to enter it.

Then you can insert into C6 cell the values separated by colons: Apples, Pears,Bananas

Note: I would follow will1329 suggestion about the named ranges.

(based on this solution)

Upvotes: 3

will1329
will1329

Reputation: 173

If you want an apporach where you can specify what to sum outside of the formula you can use

{=SUM(SUMIFS(B2:B5,A2:A5,C6:C7))}

Where C6:C7 is the values you want to lookup. (Note this needs to be an array formula so press Ctrl+Shift+Enter to enter it).

You can make the C6:C7 a named range to make it accept more or fewer fruits (see https://www.excel-easy.com/examples/dynamic-named-range.html)

Upvotes: 3

Related Questions