SUMIF with multiple criteria

[Anne Troy]

I never seem to get this right...or did they change the formula?

I want to change the following formula to sum ONLY IF there is an "A" in column A.

="MST Ob: $"&SUMIF(B4:B155,"MST",H4:H155)

I hope that someday this comes as second nature like many other formulas, but to me vlookup was much easier. I don't understand the sumproducts and the hyphens...

 

[kermitfrog123]

Try

="MST Ob: $"&SUMIFS(H4:H155,B4:B155,"MST",A4:A155,"A")

 

[Special-K99]

"sum ONLY IF there is an "A" in column A."

My glasses need a good clean but I can't see an "A" in your formula anywhere.

UPDATE: Ah, Kermit's got it!

 

[Anne Troy]

OMG, thank you so much. Now that I have it, I can update a bunch of formulas I've been dragging my feet on....

Kermit, you're the awesomest amphibian ever.

 

[Roo007]

I have a sumifs problem that i need help with!

<colgroup><col width="91"></colgroup><tbody>
</tbody>

=SUMIFS($N3:$N202,"=",BL3:BL202) + SUMIF($O3:$o202,"X",BQ3:BQ202,"<>Aux.",BL3:BL202)
The N column contains an X or is blank.
The O column contains an X or is blank.
The BL is where the Totals are at.
The BQ is where the text Aux. is at. (Some months I might have the X but not the Aux. & i still need to calculate those totals.)
=SUMIFS(BL3:BL202, $N3:$N202, "X") This is a working formula
=SUMIFS(BM3:BM202, $O3:$O202, "X",$BQ3:$BQ202,"Aux.") This is a working formula
PLEASE HELP!!!

 

[Aladin Akyurek]

I have a sumifs problem that i need help with!

<tbody>
</tbody>

=SUMIFS($N3:$N202,"=",BL3:BL202) + SUMIF($O3:$o202,"X",BQ3:BQ202,"<>Aux.",BL3:BL202)
The N column contains an X or is blank.
The O column contains an X or is blank.
The BL is where the Totals are at.
The BQ is where the text Aux. is at. (Some months I might have the X but not the Aux. & i still need to calculate those totals.)
=SUMIFS(BL3:BL202, $N3:$N202, "X") This is a working formula
=SUMIFS(BM3:BM202, $O3:$O202, "X",$BQ3:$BQ202,"Aux.") This is a working formula
PLEASE HELP!!!

The objectives are better described in words than in formulas which work half way or not at all...

Care to indicate

(1) the sum range;
(2) the target range and the criteria which must hold for that range;
(3) and repeat 2 for each additional target range.

 

[Roo007]

i'm not sure that i understand. But for simplicity the...
N column is titled Books
O column is titled Magazines
BL column is titled Returned (I'm assuming that this is my target range, where my total is)
BQ column is titled Notes (this is where the text Aux. is at & I want to calculate those cells in Books that do not have an X, & also those cells in Magazines that have an X & where Notes in that corresponding row does not contain the text Aux.

 

[Aladin Akyurek]

i'm not sure that i understand. But for simplicity the...
N column is titled Books
O column is titled Magazines
BL column is titled Returned (I'm assuming that this is my target range, where my total is)
BQ column is titled Notes (this is where the text Aux. is at & I want to calculate those cells in Books that do not have an X, & also those cells in Magazines that have an X & where Notes in that corresponding row does not contain the text Aux.

How those references are named id not important. What is important is the range to sum, the ranges, one by one, for which some criteria must hold in order to sum a corresponding figure from the range to sum.

(1) The range in column BL seems to be the range to sum if...
(2) The range in column N is not equal to an X, and
(3) The range in column O is equal to an X, and
(4) The range in column BQ does not contain Aux.

If the above specification is right, does the following work for you?

=SUMIFS(BL:BL,N:N,"<>X",O:O,"=X",BQ:BQ,"<>*Aux*")

Note the difference between (not) equal and (not) contain.

 

[Roo007]

Does this help?
Simple File Sharing and Storage.

 

[Roo007]

Here is the actual file.
sumifs