Sumproduct syntax when using AND, OR

A

adimar

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=â€â€) || ((close>D2) && ((close-open) > 30)))

Formula: =SUMPRODUCT((B1:B3<D1) AND ((C1:C3=â€â€) OR ((C1:C3>D2) AND
((C1:C3-B1:B3>30)))

What are the formula notations for AND and OR?

Thank you.
 
B

Bernie Deitrick

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

=SUMPRODUCT(((B1:B3<D1)*(C1:C3=""))+((C1:C3>D2)*(C1:C3-B1:B3>30)))

HTH,
Bernie
MS Excel MVP
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Calculating the difference between two dates if two criteria are m 3
Sumproduct checking between two dates 2
COMPARING TIMES AND DISPLAYING TEXT USING IF CONDITION 3
sumproduct with varying column ranges 1
Date/Time Question 2
Conditional Time Calculation 10
Lookup based on range of dates 1
IF condition using GMT and BST times 4

Top