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

``````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?

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)))
``````
• (I removed the Excel version as it required further tweaking) – lori_m Mar 15 '12 at 15:05

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) ))
``````