# Solved SUMIFS with two except conditions and ignore blanks

July 6, 2016 at 06:12:53
Specs: Windows 64
 Hi,I've tried this formula what seems like a million times. What I am trying to accomplish is this:Pulling from the 'Raw Data' sheetColumn G:G - has 5 different business segments (ie Light, Gas, Tube, Sun, Moon)Column O:O - has the # of each items in column G:GI am trying to come up with a formula that will give me the subtotat of all except Light and Gas (# of items is column O:O). This formual shoud ignore any blank cells in column O:O however, I do have formulas in the cells in column O:O (not sure if thats important but I figured I'd share just in case)=SUMIFS('Raw Data'!O:O,'Raw Data'!G:G,"<> Light",'Raw Data'!G:G,"<> Gas",'Raw Data'!G:G,"=Tube",'Raw Data'!G:G,"=Sun",'Raw Data'!G:G,"=Moon",'Raw Data'!G:G,"<>"))This formula returns a 0, I was expecting a total of 296?

See More: SUMIFS with two except conditions and ignore blanks

#1
July 6, 2016 at 06:51:33
 I think your formula is returning 0 because you are asking Excel to find values where the cells in Column G contain "Sun" and "Moon" and "Tube" at the same time but as separate entries. I'm sure that doesn't make sense, so look at it this way:If a cell contains Sun, then it doesn't contain Tube or Moon so the SUMIFS function doesn't think it meets your criteria. In addition (although it doesn't matter in this case) I don't think your last clause is valid: 'Raw Data'!G:G,"<>"))Edit BeginsNot only does Excel throw up an error when I paste that in, but you have not included any criteria after the <> operator.The "<>" is the correct method to ignore blank cells, although the extra parenthesis is confusing Excel. That said, I'm not sure why you are concerned with ignoring blank cells in G:G. Do you have values in O:O that correspond to blank cells in G:G? If not, there is nothing to SUM, therefore nothing to ignore.Edit EndsTry this for the criteria you have listed above. It seems to work for me:=SUM(SUMIF(G:G,{"Tube","Sun","Moon"},O:O))Although this may look like an array formula, it does not need to be entered via Ctrl-Shift-Entermessage edited by DerbyDad03

Report •

#2
July 6, 2016 at 06:56:38
 First, why do you have two ending parentheses?Second, what is your last Range_Criteria supposed to be?And remember that the =SUMIFS() function ANDs your criteria together.MIKEhttp://www.skeptic.com/

Report •

#3
July 6, 2016 at 07:00:13