Google Sheets: CountIf and Dates

6
votes

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.

  • In B2 is the date "6/25/2018", which is actually a reference to SCHEDULE!C2.
  • Am I just not understanding the logic correctly? "If the dates in C9:C29 are after or equal to 6/25/2018, then count them."
  • When I try it as <= it doesn't work either.
  • I don't think there is an issue with the dates being formatted using TEXT(B2,"MM/DD/YYYY") because other formulas seem to be working fine.

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.

1
google-sheets

1 Answers

6
votes

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