|
SUMIFS with an OR criteria
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, |
SUMIFS with an OR criteria
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*") -- Biff Microsoft Excel MVP "Go Bucks!!!" wrote in message ... 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, |
SUMIFS with an OR criteria
Of course. Bad example. I am looking to change the formula so that its A and (B or C). "T. Valko" wrote: 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*") -- Biff Microsoft Excel MVP "Go Bucks!!!" wrote in message ... 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, |
SUMIFS with an OR criteria
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) -- Biff Microsoft Excel MVP "Go Bucks!!!" wrote in message ... Of course. Bad example. I am looking to change the formula so that its A and (B or C). "T. Valko" wrote: 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*") -- Biff Microsoft Excel MVP "Go Bucks!!!" wrote in message ... 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, |
SUMIFS with an OR criteria
I was trying to do the second formula. Thanks!
"T. Valko" wrote: 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) -- Biff Microsoft Excel MVP "Go Bucks!!!" wrote in message ... Of course. Bad example. I am looking to change the formula so that its A and (B or C). "T. Valko" wrote: 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*") -- Biff Microsoft Excel MVP "Go Bucks!!!" wrote in message ... 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, |
SUMIFS with an OR criteria
Of those 2 formulas, the first is slightly more efficient (even though it's
longer and looks more complicated). Thanks for the feedback! -- Biff Microsoft Excel MVP "Go Bucks!!!" wrote in message ... I was trying to do the second formula. Thanks! "T. Valko" wrote: 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) -- Biff Microsoft Excel MVP "Go Bucks!!!" wrote in message ... Of course. Bad example. I am looking to change the formula so that its A and (B or C). "T. Valko" wrote: 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*") -- Biff Microsoft Excel MVP "Go Bucks!!!" wrote in message ... 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, |
SUMIFS with an OR criteria
I am creating varitions of your formula. What does this portion "(--" of the
formula do? Thanks "T. Valko" wrote: Of those 2 formulas, the first is slightly more efficient (even though it's longer and looks more complicated). Thanks for the feedback! -- Biff Microsoft Excel MVP "Go Bucks!!!" wrote in message ... I was trying to do the second formula. Thanks! "T. Valko" wrote: 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) -- Biff Microsoft Excel MVP "Go Bucks!!!" wrote in message ... Of course. Bad example. I am looking to change the formula so that its A and (B or C). "T. Valko" wrote: 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*") -- Biff Microsoft Excel MVP "Go Bucks!!!" wrote in message ... 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, |
SUMIFS with an OR criteria
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)))) "T. Valko" wrote: Of those 2 formulas, the first is slightly more efficient (even though it's longer and looks more complicated). Thanks for the feedback! -- Biff Microsoft Excel MVP "Go Bucks!!!" wrote in message ... I was trying to do the second formula. Thanks! "T. Valko" wrote: 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) -- Biff Microsoft Excel MVP "Go Bucks!!!" wrote in message ... Of course. Bad example. I am looking to change the formula so that its A and (B or C). "T. Valko" wrote: 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*") -- Biff Microsoft Excel MVP "Go Bucks!!!" wrote in message ... 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, |
SUMIFS with an OR criteria
See this:
http://xldynamic.com/source/xld.SUMPRODUCT.html -- Biff Microsoft Excel MVP "Go Bucks!!!" wrote in message ... I am creating varitions of your formula. What does this portion "(--" of the formula do? Thanks "T. Valko" wrote: Of those 2 formulas, the first is slightly more efficient (even though it's longer and looks more complicated). Thanks for the feedback! -- Biff Microsoft Excel MVP "Go Bucks!!!" wrote in message ... I was trying to do the second formula. Thanks! "T. Valko" wrote: 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) -- Biff Microsoft Excel MVP "Go Bucks!!!" wrote in message ... Of course. Bad example. I am looking to change the formula so that its A and (B or C). "T. Valko" wrote: 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*") -- Biff Microsoft Excel MVP "Go Bucks!!!" wrote in message ... 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, |
| All times are GMT +1. The time now is 03:57 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com