Sumproduct?
 

I just want to thank everyone that responded to my question... the
formula is great.. except that I need to be able to add an AND ex:

=SUMPRODUCT(--(sept!$C$1:$C$1000=690),--(sept!$G$1:$G$1000="expedite"))

is the current formula... but i want it to count only if it is true in
both columns.. not just one. I have some that need to look at 3 or 4
columns.. I just need to be be able to place and and or something where
it will work.

Can someone help me with that???


thanks
dawn-tx


--
imjustme
------------------------------------------------------------------------
imjustme's Profile: http://www.excelforum.com/member.php...o&userid=26854
View this thread: http://www.excelforum.com/showthread...hreadid=401234

I don't understand your description of what you want to do, but the use
of SUMPRODUCT in your example can be extended naturally to AND together
up to 30 conditions (limited by number of arguments that SUMPRODUCT can
accept).

I think you said that some of the conditions need to be be joined by OR
instead of AND. For that, you need to modify the approach slightly
=SUMPRODUCT((sept!$C$1:$C$1000=690)*(sept!$G$1:$G$ 1000="expedite"))
is equivalent to your original formula. In this version "*" functions
as AND. You could replace "*" with "+" to OR the conditions. You can
combine multiple conditions, using parentheses to control order of
evaluation. Regardless of the number of conditions combined in this
fashion, you are only using a single argument to SUMPRODUCT, so the
number of conditions in this approach is only limited by the length of
the formula.

Jerry

imjustme wrote:

I just want to thank everyone that responded to my question... the
formula is great.. except that I need to be able to add an AND ex:

=SUMPRODUCT(--(sept!$C$1:$C$1000=690),--(sept!$G$1:$G$1000="expedite"))

is the current formula... but i want it to count only if it is true in
both columns.. not just one. I have some that need to look at 3 or 4
columns.. I just need to be be able to place and and or something where
it will work.

Can someone help me with that???


thanks
dawn-tx