Formulas for COUNTIF on Fiscal Year

Syed Muhafzal

Hi Smarties,

I would like to count the number of applicants based on the Stage as below. However, I would like to only count if they fall under this fiscal year (July 2021 to June 2022).

My current formula is split to three rows (Taking Successful (R5) as an example)

This Year : =COUNTIFS({Created Date}, >TODAY(-365), {Stage}, "Successful (R5)")

This Quarter : =COUNTIFS({Created Date}, >TODAY(-90), {Stage}, "Successful (R5)")

This Month : =COUNTIFS({Created Date}, >TODAY(-30), {Stage}, "Successful (R5)")

As this is a roll-up sheet, the above formula is taking the Created Date from a system-generated column (Date Created) in a different sheet.

How do I incorporate the Fiscal Year formula as well in the above? In the other words, if today is October 2021, it should only take values from July to October (for This Year), October only (for this Quarter) and October only (for this month) - since October is the start of the Quarter.

Any advice would be greatly appreciated.

Thanks

Syed

Paul Newcome

Start of current fiscal year:

=IF(TODAY() >= DATE(YEAR(TODAY()), 7, 1), DATE(YEAR(TODAY()), 7, 1), DATE(YEAR(TODAY()) - 1, 7, 1))

End of current fiscal year:

=IF(TODAY() >= DATE(YEAR(TODAY()), 7, 1), DATE(YEAR(TODAY()), 6, 30), DATE(YEAR(TODAY()) + 1, 6, 30))

Counting between...

=COUNTIFS({Date}, AND(@cell>= start_formula, @cell<= end_formula))

=COUNTIFS({Date}, AND(@cell >= IF(TODAY() >= DATE(YEAR(TODAY()), 7, 1), DATE(YEAR(TODAY()), 7, 1), DATE(YEAR(TODAY()) - 1, 7, 1)), @cell <= IF(TODAY() >= DATE(YEAR(TODAY()), 7, 1), DATE(YEAR(TODAY()), 6, 30), DATE(YEAR(TODAY()) + 1, 6, 30))))

Paul Newcome

Adding in the cell references will actually make the formula not work correctly. The way the formula I wrote works is by referencing today's date and looking for the current fiscal year.

If you just wanted to use cell references as opposed to "live" based on today's date, it would require different (more simple) criteria.

=COUNTIFS({Created Date}, AND(@cell >= Year1, @cell <= Year2), {Realisation Stage}, "R5 - Benefit Realisation")

Paul Newcome

Start of current fiscal year:

=IF(TODAY() >= DATE(YEAR(TODAY()), 7, 1), DATE(YEAR(TODAY()), 7, 1), DATE(YEAR(TODAY()) - 1, 7, 1))

End of current fiscal year:

=IF(TODAY() >= DATE(YEAR(TODAY()), 7, 1), DATE(YEAR(TODAY()), 6, 30), DATE(YEAR(TODAY()) + 1, 6, 30))

Counting between...

=COUNTIFS({Date}, AND(@cell>= start_formula, @cell<= end_formula))

=COUNTIFS({Date}, AND(@cell >= IF(TODAY() >= DATE(YEAR(TODAY()), 7, 1), DATE(YEAR(TODAY()), 7, 1), DATE(YEAR(TODAY()) - 1, 7, 1)), @cell <= IF(TODAY() >= DATE(YEAR(TODAY()), 7, 1), DATE(YEAR(TODAY()), 6, 30), DATE(YEAR(TODAY()) + 1, 6, 30))))

Syed Muhafzal

Amazing - thanks @Paul Newcome - this is great. I have also added in new cells with Fiscal Year Start Date and Fiscal Year End Date, and incorporated these two cells in your formula above. Hopefully, this will give some flexibility to the business to choose which fiscal year they would like to view the data in.

=COUNTIFS({Created Date}, AND(@cell >= IF(TODAY() >= DATE(YEAR(Year1), 7, 1), DATE(YEAR(Year1), 7, 1), DATE(YEAR(Year1) - 1, 7, 1)), @cell <= IF(TODAY() >= DATE(YEAR(Year2), 7, 1), DATE(YEAR(Year2), 6, 30), DATE(YEAR(Year2) + 1, 6, 30))), {Realisation Stage}, "R5 - Benefit Realisation")

Syed Muhafzal

@Paul Newcome

One more thing, if I want to have the formula looking at the current quarter in the current fiscal year, is that possible? Do I need to expand the formula to also include Month 1-3, 4-6, 7-9, 10-12?

Paul Newcome

Adding in the cell references will actually make the formula not work correctly. The way the formula I wrote works is by referencing today's date and looking for the current fiscal year.

If you just wanted to use cell references as opposed to "live" based on today's date, it would require different (more simple) criteria.

=COUNTIFS({Created Date}, AND(@cell >= Year1, @cell <= Year2), {Realisation Stage}, "R5 - Benefit Realisation")

Syed Muhafzal

Thanks so much (as always) @Paul Newcome

I will try to figure out how to add Quarter within the same fiscal year in that formula :D