CODEWITHSUNDEEP
google-sheets
N.O.Davis
N.O.Davis

Reputation: 513

Google Sheets: CountIf and Dates

Haven't gotten any help through the Google Product Forums, so I'll try here. I've never had issues with this before so not sure what I'm missing now. I can't seem to make a rather simple formula work and I have no idea why.

For some reason, =COUNTIF(SCHEDULE!$C$9:$C29,">="&B$2) returns 0, when I'm expecting it to return 21.

Alternatively, I also tried using SUM(QUERY), but that also got hung up when using the dates.

=sum(query(SCHEDULE!A9:AB,"select count(F) where F='2004' and C >= date '"&TEXT(DATEVALUE(B2),"yyyy-mm-dd")&"'",0))

You can see a test version of the sheet HERE.

I am working from the REF sheet.

You can see what I've tried in Z6, Z7, Z8 and AA6.

Any suggestions would be appreciated. I feel like I'm missing something obvious. I'm not sure if it's the fact that some of the dates from the SCHEDULE page are rendered using TEXT, but I can't figure this out.

Upvotes: 6

Views: 26779

Answers (1)

user4039065
user4039065

Reputation:

Ref!B2 is TEXT(SCHEDULE!$C$2,"MM/DD/YYYY"). You cannot compare text-that-looks-like-a-date to true dates.

The best option is to use =SCHEDULE!$C$2 in REF!B2 and format the cell as mm/dd/yyyy.

The bandaid (dig a hole then fill it up) is to adjust your COUNTIF to,

=COUNTIF(SCHEDULE!$C$9:$C29,">="&DateValue(B$2))

Upvotes: 6

Related Questions