How to write a logical function that checks several columns and rows

Question

I have three columns in my Google Spreadsheet:

C (Dialog) - D (Declined) - E(Answered)

All the columns are dates. Example:

C                    D                   E

14/02/2012
28/02/2012
14/02/2012       14/03/2012
28/02/2012                           07/03/2012
14/02/2012                           28/02/2012
                 21/02/2012          
15/02/2012
21/02/2012
15/02/2012                          14/03/2012
15/02/2012                          22/02/2012
21/02/2012                          27/02/2012
21/02/2012       05/03/2012
28/02/2012
                 28/02/2012
15/02/2012
28/02/2012       14/03/2012
13/02/2012       14/03/2012
14/02/2012                          14/03/2012

I am trying to count how many people i was in dialogue with from the 10/02/2012 (10'th of febuary).

I am in dialogue with someone, when column C is less than or equal to the date that i am matching with, and D is either blank or great than the date, or E is blank or greather than the date.

So far this is what i have created, but it doesnt count it right:

=ARRAYFORMULA(SUM(if(if($C$4:$C$4000 <= H55; 1; 0)+if($C$4:$C$4000 <> ""; 1; 0)+if(OR($D$4:$D$4000 = ""; $D$4:$D$4000 > H55);1;0)+if(OR($E$4:$E$4000 = ""; $E$4:$E$4000 > H55);1;0)=4; 1; 0)))

Where H55 is 10/02/2012 and all the below cells in H are H55+1

The function counts that i was in dialogue with 12 the 21/02/2012 - which is correct. There are 12 rows in column C where the date <= 21/02/2012

When i get to the 22'th, there is a cell in column E that is not greater than than C, which means that the 12 should be counted down to 11, but it still displays 12.

Where i am doing it wrong?

Answer 1

Logical operators such as OR return single values in array formulas, try instead:

=ARRAYFORMULA(SUM(IF(IF($C$4:$C$4000 <= H55; 1; 0)+IF($C$4:$C$4000 <> ""; 1; 0)+IF(($D$4:$D$4000 = "")+($D$4:$D$4000 > H55);1;0)+IF(($E$4:$E$4000 = "")+( $E$4:$E$4000 > H55);1;0)=4; 1; 0)))

Answer 2

Create a new column (F) that will have the value of D or E or NOW() if both are blank

=IF(ISBLANK(D2),IF(ISBLANK(E2),NOW(),E2),D2)

Then use a product with a simpler criteria

=ARRAYFORMULA(SUM(((C:C)<H55) * ((F:F)>H55) * (1) ))