COUNTIF with dates and years

Tessa Heath · 01/22/19

Hello,

I am trying to write a formula to count executed leases for 2019 and 2020. I do not have a lot of experience writing long formulas but basically the logic is count if there is a date in the Lease Signed field AND if grand opening is between 1/1/19 to 12/31/19 or 1/1/2020 to 12/31/2020. Can anyone provide some guidance or recommendations to try? Here's a screenshot of my sheet:

Smartsheet Question (1.22.19).PNG

Brian W · 01/22/19

COUNTIF checks for a single condition, while COUNTIFS checks for multiple conditions. Try this:

=COUNTIFS([Lease Signed]:[Lease Signed], ISDATE(@cell), [Grand Open Date]:[Grand Open Date], >=DATE(2019, 1, 1), [Grand Open Date]:[Grand Open Date], <=DATE(2020, 12, 31))

Paul Newcome · 01/24/19

Here is another variation on Brian's formula.

=COUNTIFS([Lease Signed]:[Lease Signed], ISDATE(@cell), [Grand Open Date]:[Grand Open Date], OR(YEAR(@cell) = 2019, YEAR(@cell) = 2020))

COUNTIF with dates and years

Comments

Help Article Resources

Categories