Solved SUMIFS with two except conditions and ignore blanks

July 6, 2016 at 06:12:53
Specs: Windows 64

I've tried this formula what seems like a million times. What I am trying to accomplish is this:

Pulling from the 'Raw Data' sheet
Column G:G - has 5 different business segments (ie Light, Gas, Tube, Sun, Moon)
Column O:O - has the # of each items in column G:G

I 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

Reply ↓  Report •

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 Begins

Not 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 Ends

Try this for the criteria you have listed above. It seems to work for me:


Although this may look like an array formula, it does not need to be entered via Ctrl-Shift-Enter

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03

Reply ↓  Report •

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.


Reply ↓  Report •

July 6, 2016 at 07:00:13
✔ Best Answer
DerbyDad got here first.

As an alternative to DeryDad's formula, you could also use this formula:



message edited by mmcconaghy

Reply ↓  Report •

Related Solutions

July 6, 2016 at 09:23:39
mmcconaghy - Believe it or not after hitting the enter key, a box popped up asking me to accept a "corrected" version of my formula. I clicked YES and the two ending parentheses was added (I really can't be 100% certain b/c Ive tried this formula several times, but I do believe Excel added it?)

I used this formula =SUMIFS(O:O,G:G,"<>Light",G:G,"<>Gas") and it worked like a charm!!

Thank you both!!!

Reply ↓  Report •

Ask Question