COUNTIF formula to count how many projects the past 2 years? The date is listed as 02/03/19

Abbie G

and I would like to go back the past 2 years.

Paul Newcome

Ok. You are going to either want to convert it to a date type column with date type data, or we will need to use a date type helper column with a formula to convert it. The first option is most likely going to be the least complicated.

Once we have date type values, we can use

=COUNTIFS([Date Column]:[Date Column], AND(@cell >= DATE(YEAR(TODAY()) - 2, MONTH(TODAY()), DAY(TODAY())), @cell <= TODAY()))

Paul Newcome

To pull unique IDs, you are going to want something more like this...

=COUNT(DISTINCT(COLLECT([ID( Column]:[ID Column], [Date Column]:[Date Column], AND(@cell >= DATE(YEAR(TODAY()) - 2, MONTH(TODAY()), DAY(TODAY())), @cell <= TODAY()))))

Paul Newcome

Try some "@cell" references in the OR function.

=COUNT(DISTINCT(COLLECT({Proj. No.}, {Sheet}, OR(@cell = "Renewals / Extensions", @cell = "Renewals/Extensions"), {Lease Exp. Date}, AND(@cell >= DATE(YEAR(TODAY()) - 2, MONTH(TODAY()), DAY(TODAY())), @cell <= TODAY()))))

Paul Newcome

Happy to help. 👍️

Paul Newcome

Are the dates in a date type column and entered as date type data (as opposed to a text string that just looks like a date)?

Are you wanting a rolling two years from TODAY, or are you wanting to specify a year or years?

Abbie G

@Paul Newcome The column property is a text/number.. hopefully, that answers your question. And yes it'd be great to count from TODAY the past 2 years. Thank you!

Paul Newcome

Ok. You are going to either want to convert it to a date type column with date type data, or we will need to use a date type helper column with a formula to convert it. The first option is most likely going to be the least complicated.

Once we have date type values, we can use

=COUNTIFS([Date Column]:[Date Column], AND(@cell >= DATE(YEAR(TODAY()) - 2, MONTH(TODAY()), DAY(TODAY())), @cell <= TODAY()))

Abbie G

Thank you so much @Paul Newcome! Also, how do I add on to that formula so that it counts each unique number IDs?

Paul Newcome

To pull unique IDs, you are going to want something more like this...

=COUNT(DISTINCT(COLLECT([ID( Column]:[ID Column], [Date Column]:[Date Column], AND(@cell >= DATE(YEAR(TODAY()) - 2, MONTH(TODAY()), DAY(TODAY())), @cell <= TODAY()))))

Abbie G

Thank you so much @Paul Newcome!!

Paul Newcome

Happy to help. 👍️

Abbie G

Sorry, I have a follow up question

=COUNT(DISTINCT(COLLECT({Proj. No.}, {Sheet}, OR("Renewals / Extensions", "Renewals/Extensions"), {Lease Exp. Date}, AND(@cell >= DATE(YEAR(TODAY()) - 2, MONTH(TODAY()), DAY(TODAY())), @cell <= TODAY()))))

I want to count all the rows that have "Renewals / Extensions" or "Renewals/Extensions" on the Sheet column but it's only coming up as 1. Do you have any recommendations so that it counts all the rows?

Paul Newcome

Try some "@cell" references in the OR function.

=COUNT(DISTINCT(COLLECT({Proj. No.}, {Sheet}, OR(@cell = "Renewals / Extensions", @cell = "Renewals/Extensions"), {Lease Exp. Date}, AND(@cell >= DATE(YEAR(TODAY()) - 2, MONTH(TODAY()), DAY(TODAY())), @cell <= TODAY()))))

Abbie G

Thank you @Paul Newcome!!

Paul Newcome

Happy to help. 👍️