I have a list of trouble tickets with ticket#, open and close dates recorded
like this:

A B C
1234 05/01/07 06/15/07
4567 04/01/07 07/15/07
4321 05/29/07

Timeframe is recorded like this:
D1=06/01/07
D2=06/30/07

I am trying to write the condition below as a SUMPRODUCT.

Condition: (open<D1) && ((close=€�€�) || ((closeD2) && ((close-open) 30)))

Formula: =SUMPRODUCT((B1:B3<D1) AND ((C1:C3=€�€�) OR ((C1:C3D2) AND
((C1:C3-B1:B330)))

What are the formula notations for AND and OR?

Thank you.

Multiplication is logical AND, addition is logical OR. Just watch your parens to group things
properly.

=SUMPRODUCT(((B1:B3<D1)*(C1:C3=""))+((C1:C3D2)*(C 1:C3-B1:B330)))

HTH,
Bernie
MS Excel MVP


"adimar" wrote in message
...
I have a list of trouble tickets with ticket#, open and close dates recorded
like this:

A B C
1234 05/01/07 06/15/07
4567 04/01/07 07/15/07
4321 05/29/07

Timeframe is recorded like this:
D1=06/01/07
D2=06/30/07

I am trying to write the condition below as a SUMPRODUCT.

Condition: (open<D1) && ((close="") || ((closeD2) && ((close-open) 30)))

Formula: =SUMPRODUCT((B1:B3<D1) AND ((C1:C3="") OR ((C1:C3D2) AND
((C1:C3-B1:B330)))

What are the formula notations for AND and OR?

Thank you.

addition is logical OR ??
http://www.freefilehosting.net/download/3bhi3

Made a typo in the formulas.
Both methods agree now.
http://www.freefilehosting.net/download/3bhj0