Sum multiple criteria from the same column SUM(SUMIFS(???

[Silverjman]

I need a formula that will sum a column given four different types of criteria, fine, Aladin's

=SUMIFS(U$4:U$4783,$C$4:$C$4783,IF($A$4804="All","<>",$A$4804........................)) is awesome.

Now of course, boss man wants multiple criteria from the same column be able to be selected and summed.

ERROR{=SUM(SUMIFS(U$4:U$4783,$C$4:$C$4783,{$C$4804,$C$4805,$C$4806,}))}ERROR
OK{=SUM(SUMIFS(U$4:U$4783,$C$4:$C$4783,{"GREEN","BLUE","RED",}))} OK I assume this has somehting to do with arrays accepting text but not references, I tried adding " "s to no avail.

However I need those cells to be live because the user is going to be switching GREEN, BLUE, and RED (plus 13 more) on or off depending on what they want analyse. I'm dividing the sums to generate returns so I'm really looking for a formula/function solution to this...I guess I would also need the formula to work if the criteria was BLANK hmmmm

I wasn't able to find a solution from the below. Any help or just that I'm barking up the wrong tree would be much appreciated.

http://www.mrexcel.com/forum/excel-questions/555915-sumifs-multiple-criteria-same-column.html
http://www.mrexcel.com/forum/excel-...-multiple-columns-produce-unique-results.html http://www.mrexcel.com/forum/excel-questions/836455-formula-explanation-sum-sumifs.html

 

[shawnhet]

You try something like the following expression as a helper row:

=IF(ISERROR(HLOOKUP(C2,Sheet2!$A$1:$R$1,1,FALSE)),"not selected","selected")

the red section is a range of cells where your user will input all the criteria he is interested in getting the sums for (I've assumed that the criteria in question are names or values on that list).

Then you can add a criteria to you SUMIF that sums the selected columns from your helper row.

Cheers,