Need a sumifs with an "or" in one of the criteria...??

[pshearer]

Guys; This seems like it should be simple but I'm stumped nevertheless.

I attached the data.

Note the red cell near the top left. What I have done is created a sumifs formula to basically say "sum the total in column J if the name in column B is "mrobledo", if the code in column T includes "CYO" and if the value in column P is blank."

All I need is for this formula to be modified so that it says:

"sum the total in column J if the name in column B is "mrobledo", if the code in column T includes "CYO" and if the value in column P is blank or includes the word "New."

That's it. The formula is right....all I need is for the criteria in column P to sum if it is blank OR includes the word "New."

Thanks for any help. I've been trying brackets and all kinds of logic, but can't make anything work. Thanks again.

[newdoverman]

This should do it if I understand you correctly:

Please Login or Register  to view this content.

[Tony Valko]

Try this...

=SUM(SUMIFS(J:J,T:T,"CYO*",B:B,"mrobledo",P:P,{"","New"}))

[newdoverman]

This could work for you too: -----nope!! Doesn't

Array Enter the following (Ctrl + Shift + Enter)

Please Login or Register  to view this content.

[popipipo]

An alternative way:
Make use of filter and subtotal formula

[Tony Valko]

Array Enter the following (Ctrl + Shift + Enter)

=SUMIFS(J:J,T:T,"CYO*",B:B,"mrobledo",P:P,{"","New"})

That doesn't work for me.

..........B............P..........J..........T......
1..mrobledo....New.......10......CYO xx
2..mrobledo.................20......CYO xx

P2 = an empty cell

Array entered:

=SUMIFS(J:J,T:T,"CYO*",B:B,"mrobledo",P:P,{"","New"})

Returns 20

The correct result should be 30.

=SUM(SUMIFS(J:J,T:T,"CYO*",B:B,"mrobledo",P:P,{"","New"}))

Correctly returns 30.

[newdoverman]

@ Tony thanks, just verified that myself.

This, I have tested and it, in my sleepy haze, seems to work fine

Please Login or Register  to view this content.

[Tony Valko]

Yeah, that'll work...

But, the SUM/SUMIFS version is much more efficient!

[newdoverman]

Just giving alternatives.