Counting total rows that fall after a specific Date

E_Pieper

I am looking to create a formula that will capture all active rows that I  have that fall withhin a Fiscal Year of 10/1/17 through 9/30/18. As of right now for the new fiscal year of 10/1/18-9/30/19 I have the following equation: =COUNTIFS({End Date}, >=DATE(2018, 10, 1), {Doc Type}, "Master")

I am looking to capture the previous fiscal years as well without "double dipping"

 

Beth

Mike Wilday

Hi Beth,

To prevent double dipping I would also add a greater than date... so you are limiting your return to everything that falls within two dates. 

=COUNTIFS({End Date}, >=DATE(2018, 10, 1), {End Date}, <=Date(2019, 09, 30), {Doc Type}, "Master")

Paul Newcome

Good suggestion Mike. Another option would be to add a Fiscal Year column to the reference sheet and use a COUNTIFS on that.

E_Pieper

Paul, 

How would you suggest setting up a Fiscal Year column? This idea intrigues me. Would you use an equation that would update to the correct Fiscal Year if the End Date changes?

We are using this specific sheet to track contractual agreements and their amendments. I have the amendments as children of the master agreement row. 

Beth

Paul Newcome

My apologies. When I read Fiscal Year, for whatever reason I had Quarter in my head.

 

Building a FY table that will update the FY for the row based off of the End Date is possible, but with my new found realization (duh moment) I believe Mike's solution to be the easiest route for getting your counts.

Paul Newcome

Beth,

 

Yesterday was crazy, and apparently I was totally overthinking things. To have an updating fiscal year, you simply need the formula below...

 

=IF(MONTH([End Date]@row) < 10, "FY" + YEAR([End Date]@row), "FY" + (YEAR([End Date]@row) + 1))

 

The result will be FY2018 for any end date that falls before October of 2018. If the month for the date is October or later, it will be FY2019. It is based off of the month and year of the date, so it will continue to evolve regardless of how many years in the future or past the date is.

E_Pieper

Thanks. That is awesome. I wonder if you can assist on a relating question...

I am trying to capture some statistics. Once of which being the total partner types we have entered into contracts with throughout the Fiscal Year. I am able to use your fiscal year trick for the parent row, but for those contracts that have more than one partner (listed on a child row), I cannot count the partner type because it does not capture the fiscal year. If I add the fiscal year equation to the children rows, it alters the total Active Agreement calculation I have.

Active Agreement Calculation: =COUNTIF({Active through FY},>="FY2019")

Active FY Partner Total = =COUNTIFS({Active Through}, >="FY2019", {Partner Type}, "College/University")

Any advice?