Excel Sumifs array with multiple criteria within 1 cell

1
votes

I’m trying to write a formula to sum multiple criteria using an array, but I want to have the criteria in one cell. Normally, the criteria within the array are manually entered, but I want to be able to make it dynamic so that I could have 3 colour or however many I want to add together. I don’t want to put them in separate cells as the spreadsheet would explode in the number of columns required.

Example: Sums together both Blue and Yellow. 

{ =sum(sumifs($a$1:$a$10,$b$1:$b$10,{“Blue”,”Yellow”}))}

Column a contains colours, column b contains numbers.

What I’d like to do is have one cell that contains the criteria of Blue, Yellow and have a formula that looks like this:

{=sum(sumifs($a$1:$a$10,$b$1:$b$10,{c1}))}  where c1  would be “Blue”, “Yellow”
1
exceldynamic-arrayssumifs

1 Answers

2
votes

Unfortunately doing what you're asking is rather complicated. It's much easier to split up the criteria into multiple cells. For example, if Blue was in cell C1 and Yellow was in cell C2, then the formula would be (regular formula, no array entry necessary):

=SUMPRODUCT(SUMIFS($A$1:$A$10,$B$1:$B$10,$C$1:$C$2))

But if Blue,Yellow is inside a single cell, C1, then this is the formula you would need. It's kind of long and ugly (regular formula, no array entry necessary):

=SUMPRODUCT(SUMIFS($A$1:$A$10,$B$1:$B$10,TRIM(INDEX(MID(SUBSTITUTE($C$1,",",REPT(" ",LEN($C$1))),LEN($C$1)*(ROW($A$1:INDEX($A:$A,LEN($C$1)-LEN(SUBSTITUTE($C$1,",",""))+1))-1)+1,LEN($C$1)),))))

So basically, please don't put multiple criteria in a single cell. Much much easier and cleaner to put the criteria in separate cells.