SUMIFS with an OR criteria

G

Go Bucks!!!

I currently have the formula:
=SUMIFS('Labor Reclasses'!$D$6:$D$8000,'Labor
Reclasses'!$M$6:$M$8000,Summary!$A4,'Labor
Reclasses'!$F$6:$F$8000,"Travel-Non-Dispatch",'Labor
Reclasses'!$F$6:$F$8000,"Travel"))

I want to change the last two criteria to an OR. Thus the value in column D
would be added for all cases where Column M matches Summary!A4, and Column F
is either "Travel-Non-Dispatch" or "Travel"

Thanks,
 
T

T. Valko

If Travel-Non-Dispatch and Travel are the only variations that contain the
string Travel, then:

=SUMIFS('Labor Reclasses'!$D$6:$D$8000,'Labor
Reclasses'!$M$6:$M$8000,Summary!$A4,'Labor
Reclasses'!$F$6:$F$8000,"Travel*")
 
G

Go Bucks!!!

Of course. Bad example. I am looking to change the formula so that its A
and (B or C).
 
T

T. Valko

Try something like this:

=SUMPRODUCT(--(Rng1="A"),--(ISNUMBER(MATCH(Rng2,{"B","C"},0))),Sum_Rng)

Or:

=SUMPRODUCT(--(Rng1="A"),(Rng2="B")+(Rng2="C"),Sum_Rng)
 
G

Go Bucks!!!

I was trying to do the second formula. Thanks!

T. Valko said:
Try something like this:

=SUMPRODUCT(--(Rng1="A"),--(ISNUMBER(MATCH(Rng2,{"B","C"},0))),Sum_Rng)

Or:

=SUMPRODUCT(--(Rng1="A"),(Rng2="B")+(Rng2="C"),Sum_Rng)
 
T

T. Valko

Of those 2 formulas, the first is slightly more efficient (even though it's
longer and looks more complicated).

Thanks for the feedback!
 
G

Go Bucks!!!

I am creating varitions of your formula. What does this portion "(--" of the
formula do?

Thanks
 
G

Go Bucks!!!

This is the formula I made...

=SUMPRODUCT(('Call Activity'!D:D=$C$10)*('Call
Activity'!B:B=$D$13)*(ISNUMBER(MATCH('Call Activity'!O:O,{0,1},0))))
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top