Sumproduct syntax when using AND, OR
 

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.

Sumproduct syntax when using AND, OR
 

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.

Sumproduct syntax when using AND, OR
 

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

Sumproduct syntax when using AND, OR
 

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