1

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.

Dominique
  • 16,450
  • 15
  • 56
  • 112
PJC83
  • 193
  • 1
  • 3
  • 13
  • Are you wanting to use this approach because the size of your array varies? – SJR Apr 25 '19 at 12:05
  • Yes, apologies, I should have been clearer on that. Basically, I am analysing around 30000 records, and pulling out totals on them. Certain items go by multiple names, so I have a couple of search strings such as the {"Apples","Bananas} above. – PJC83 Apr 25 '19 at 12:08
  • I know I could have manually entered the data in the time I've spent looking for the solution, but that feels like admitting defeat. – PJC83 Apr 25 '19 at 12:09
  • 1
    Yes I see. If you put the separate items into separate cells you could use , eg. `=SUM(SUMIFS(B2:B5,A2:A5,C6:C7))` as an array formula. I think in essence that's will1329's answer. – SJR Apr 25 '19 at 12:11
  • You can have all the criteria in a single cell, just check my answer :) – RCaetano Apr 25 '19 at 12:50

3 Answers3

3

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)

will1329
  • 173
  • 2
  • 14
3

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)

RCaetano
  • 642
  • 1
  • 8
  • 23
  • This is excellent, thank you and Will for the responses, this is exactly what I wanted. – PJC83 Apr 25 '19 at 13:11
0

Formula:

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

Results:

enter image description here

Error 1004
  • 7,877
  • 3
  • 23
  • 46
  • 1
    You are hardcoding the values and it seems that OP wants to change them without needing to change the formula... – RCaetano Apr 25 '19 at 12:15
  • @RCaetano the OP has to change the hard coded string or the cell. in both cases needs modifications – Error 1004 Apr 25 '19 at 12:55
  • You edited your answer before posting your last comment, but my comment was right at the time; Nevertheless, you had an hard coded `"A"` and `"B"` and then you changed it to `A1` and `B1`, so now is not hardcoded anymore – RCaetano Apr 25 '19 at 13:12
  • @RCaetano i change the answer according to OP requirement (you mention above). in both case the OP has to make changes! When the values are hard coded the OP should make changes in the formula changing "A" & "B" and If the values are NOT hard coded the OP has to change either "A1" & "B1" to other cells OR change the values in cells A1 & B1. In both cases there will be changes! – Error 1004 Apr 25 '19 at 13:17
  • I will not discuss this much further because is more a semantic question than anything else. What I wanted to highlight is that it is considered hardcoded when you need to change code/formula, not an input/cell value (I know they may seem the same thing but they are not) If you want check https://stackoverflow.com/questions/1895789/what-does-hard-coded-mean :) – RCaetano Apr 25 '19 at 13:26