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' 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: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?

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 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 notneed 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

First, why do you have two ending parentheses?

Second, what is yourlastRange_Criteria supposed to be?And remember that the =SUMIFS() function ANDs your criteria together.

MIKE

DerbyDad got here first. As an alternative to DeryDad's formula, you could also use this formula:

=SUMIFS(O:O,G:G,"<>Light",G:G,"<>Gas")

MIKE

message edited by mmcconaghy

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!!!

Ask Your Question

Weekly Poll

How long do you think until flying cars or taxis are common?

Discuss in The Lounge

Poll History