Google Sheets – How to Write a Logical Function to Check Multiple Columns and Rows

google sheets

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?

Best Answer

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