COUNTIF with dates and years

Tessa Heath

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:

 

 

Brian W

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

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