Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

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?

share|improve this question
add comment

migrated from stackoverflow.com Mar 15 '12 at 23:05

This question came from our site for professional and enthusiast programmers.

2 Answers

up vote 3 down vote accepted

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)))
share|improve this answer
    
(I removed the Excel version as it required further tweaking) –  lori_m Mar 15 '12 at 15:05
    
Works very good. THanks! –  Kenci Mar 15 '12 at 19:51
add comment

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) ))
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.