SumProduct: multiple variables and skip when criteria does not match

[KP_SoCal]

I’m struggling to come up with a solution for this. I have two questions which I will create separate posts for. For this post, here’s the formula I’m working with:

=SUMPRODUCT((AllocatedVeh!$A$2:$A$65000=Manifest!C8)*(AllocatedVeh!$C$2:$C$65000=Manifest!D8)*(AllocatedVeh!$F$2:$F$65000=Manifest!G8)*(AllocatedVeh!$I$2:$I$65000))

I need my SUMPRODUCT function formula to SKIP criteria in certain instances. For instance, if Manifest!C8 = “TEXTILE”, I want to skip (AllocatedVeh!$A$2:$A$65000=Manifest!C8) as being a part of the criteria in summing (AllocatedVeh!$I$2:$I$65000).

I hope this makes sense. Thanks so much for any help you can provide!

 

[njimack]

Does this do the trick?

=SUMPRODUCT(--(AllocatedVeh!$A$2:$A$65000<>"TEXTILE"),--(AllocatedVeh!$A$2:$A$65000=Manifest!C8),--(AllocatedVeh!$C$2:$C$65000=Manifest!D8),--(AllocatedVeh!$F$2:$F$65000=Manifest!G8),(AllocatedVeh!$I$2:$I$65000))

By the way - do you really need to reference 65,000 rows?

 

[KP_SoCal]

No, this does not accomplish what I'm looking for. I still need to reference Manifest!C8 for instances when Manifest!C8 <> "Textile" then include this criteria in the formula, but when it is equal to "Textile", I want it to skip this criteria and move on to the next criteria in the formula.

I think the answer maybe within the "MATCH" function, but I don't have much experience with using it, especially within a SUMPRODUCT formula. I searched other forums for this, but am still coming up short.

Any other sugguestions???

 

[sous2817]

How about:

=IF(Manifest!C8="Textile",SUMPRODUCT((AllocatedVeh!$C$2:$C$65000=Manifest!D8)*(AllocatedVeh!$F$2:$F$65000=Manifest!G8)*(AllocatedVeh!$I$2:$I$65000)),SUMPRODUCT((AllocatedVeh!$A$2:$A$65000=Manifest!C8)*(AllocatedVeh!$C$2:$C$65000=Manifest!D8)*(AllocatedVeh!$F$2:$F$65000=Manifest!G8)*(AllocatedVeh!$I$2:$I$65000))

You may have to tweak the () a bit, but basically it's an "IF" function that doesn't have the "SUMPRODUCT((AllocatedVeh!$A$2:$A$65000=Manifest!C8)" if Manifest!C8 = "Textile"

Is that what you had in mind?

 

[Aladin Akyurek]

I’m struggling to come up with a solution for this. I have two questions which I will create separate posts for. For this post, here’s the formula I’m working with:

=SUMPRODUCT((AllocatedVeh!$A$2:$A$65000=Manifest!C8)*(AllocatedVeh!$C$2:$C$65000=Manifest!D8)*(AllocatedVeh!$F$2:$F$65000=Manifest!G8)*(AllocatedVeh!$I$2:$I$65000))

I need my SUMPRODUCT function formula to SKIP criteria in certain instances. For instance, if Manifest!C8 = “TEXTILE”, I want to skip (AllocatedVeh!$A$2:$A$65000=Manifest!C8) as being a part of the criteria in summing (AllocatedVeh!$I$2:$I$65000).

I hope this makes sense. Thanks so much for any help you can provide!

Try...

=SUMPRODUCT(
   --(AllocatedVeh!$A$2:$A$65000=
        IF(Manifest!C8 = “TEXTILE”,AllocatedVeh!$A$2:$A$65000,Manifest!C8),
   --(AllocatedVeh!$C$2:$C$65000=Manifest!D8),
   --(AllocatedVeh!$F$2:$F$65000=Manifest!G8)
   AllocatedVeh!$I$2:$I$65000)

 

[KP_SoCal]

Aladin Akyurek, thanks so much! This is exactly what I was looking for!!!

I can easily apply this logic to other criteria in my formula without creating large IF statements.

As I mentioned in my post, I had two questions. You answered my first one. I just created a new post for my second question at the following link: http://www.mrexcel.com/forum/showthread.php?p=2125027#post2125027

Thanks again!!!