# Countif with Dates

Options

Hi all,

I've read notes on other posts with similar dates but haven't been able to figure it out so asking here. Sorry for the repetition. I'm trying to count the number of cells in a column of dates that have a date prior to a date I have stored in another cell. I'm actually doing it with countifs but I can get the other parts to work, just not the date.

The Zero in row 4 of Column 16 is from the below equation. Clearly not accurate

=COUNTIF([Target Hire Date]:[Target Hire Date], "<08/01/2021")

The #UNPARSE error in row 5 of Column 16 is from a different attempt to rather than input the date, reference the date stored in Column 16 row 2.

=COUNTIF([Target Hire Date]1:[Target Hire Date]46,"<"&[Column16]2)

It's really frustrating because I can do this in 2 seconds in excel but it's not working in SmartSheets. In case it matters I do have some blank cells in the column Target Hire Date but when I tried it selecting only the cells with data it still didn't work.

Thanks for any help here!

Jen

Tags:

• ✭✭✭✭✭✭
Options

Hope you are fine, please try the following formula:

=COUNTIF([Target Hire Date]:[Target Hire Date], @cell < Date(2021,08,01))

bassam.khalil2009@gmail.com

• ✭✭✭✭✭✭
Options

Hope you are fine, please try the following formula:

=COUNTIF([Target Hire Date]:[Target Hire Date], @cell < Date(2021,08,01))

bassam.khalil2009@gmail.com

• edited 07/22/21
Options

@Bassam Khalil. When you type @cell, what does that actually mean? Do I type that or do I reference a specific cell there? Thanks! And thank you for the quick reply :)

• ✭✭✭✭✭✭
Options

@cell is a function used inside other functions like SUMIFS, COUNTIFS to make it check the criteria in the selected range cell by cell. Please try to use my formula as is it

bassam.khalil2009@gmail.com

• Options

@Bassam Khalil. Got it. Thanks for the explanation. I hadn't realized it was a function in itself and I like to understand so I can apply going forward in new situations. Really appreciate the insights here!!

• ✭✭✭✭✭✭
Options