Use SUMIF and IF together

[cubs610]

Hi Everyone,

I got a question from a coworker, it's a little convoluted so I copied and pasted it verbatim. I'm thinking to solve this the formula should be a nested IF and SUMIF. Love to hear your thoughts. Please see below.

---------

Trying to use some different functions. I want to add together what is found in column AO every time the supervisor of Tom or Sharon appears. I can get it to work when I have just one criteria but when I add the second one it returns a result of 0. What do I have wrong in my formula? Thanks for the help. I just copied my formula, obviously my rows run from 32 to 70 and I am trying to use this function as column c contains people that I don’t want the result to add.



=SUMIFS(AO32:AO70,C32:C70,"TOM*",C32:C70,"SHARON*")



Column c Column AO

Tom Strycharz

7.50

Denise Cates

0.00

Sharon Testa


Thanks,
Dave
7.50

 

[Somendra Misra]

Hi Dave,

SUMIFS works if all the conditions will be true. So in Column C Cell either it can be something with Sharon or with TOM, but now both that's why formula is not working.

Try below formula, change the range.

=SUMPRODUCT((L2:L4)*ISNUMBER(SEARCH({"TOM*","SHARON*"},C2:C4)))

Here column L cells has value and column C cells has names.

Regards,

 

[Luke M]

Since're they are exclusive criteria (ie, cell will have either Tom or Sharon or neither), you can add results like:
=SUMIF(C32:C70,"Tom*",AO32:AO70)+SUMIF(C32:C70,"Sharon*",AO32:AO70)

SM's formula is equally valid, and has advantage of using less cell references. Go with whichever one you understand better (so that you can edit it in future).

 

[cubs610]

Hey Somendra and Luke. Definitely appreciate the efforts!!

@Somendra -- that is a great formula!
@Luke M-- I'm digging the Occam's razor action!

Thanks,
Dave

 

[Somendra Misra]

Welcome back anytime!!!

Regards,