Counting total rows that fall after a specific Date

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

Find more posts tagged with

Formulas

Cell linking

Comments

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?

Sign in to join the conversation!

Quick Links

Trending discussions

Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?
Need Help with If, Index, Collect
Please help, I have a few of these to do. I have a nested IF formula. The first part is an INDEX/MATCH and works as a standalone. The next few are INDEX/COLLECT. These are not working as standalone or in the nested version (Unparsable Error). Any ideas? =IF(CONTAINS(“A/V”, [Vendor Type]@row ), INDEX(COLLECT({Date Secured…