CountIFS based on rolling FY and item.

Samantha Baruah

Hi Everyone:

I am trying to count how many of X has been completed (based on completion date) in the current FY based. Ideally I'd like the count to automatically update what the current FY is.

I think this should be a simple formula, but for some reason, I'm at a loss.

Thanks in advance for your help.

KPH

Hi @Samantha Baruah

So you have a column with completion date and you need to count how rows have completion dates that fall into the current FY.

You can use a COUNTIFS function with the two criteria being the start and end of the fiscal year. And then a couple of IF formula to work out the start and end of the fiscal year.

Step 1 - Formula to count rows between two dates

To start with you need a COUNTIFS that would look something like this:

=COUNTIFS([Completion Date]:[Completion Date], >=DATE(2024, 4, 1), [Completion Date]:[Completion Date], <=(DATE(2025, 3, 31)))

I have used Completion Date as the name of the completion date column and a fiscal year starting April 1, 2024, ending Mar 31, 2024. So the formula is counting all the rows where the completion date is on or after April 1 2024 and also on of before Mar 31 2025.

Step 2 - Formula to work out the start of the current fiscal year (from today's date)

To make this work with the current year, instead of putting a fixed date in as the start and end dates you can use the TODAY function to calculate these dates based on today's date.

The formula would look something like this:

=IF(MONTH(TODAY()) >= 4, DATE(YEAR(TODAY()), 4, 1), DATE(YEAR(TODAY()) - 1, 4, 1))

I am again using April 1st as the start of the year and assuming anything from April 1st, 2023 is FY 2024, from April 1st 2024, is FY 2025. You can change these dates.

The formula basically looks at todays date TODAY()

Extracts just the month

MONTH(TODAY())

If this is equal to or less than 4, then it creates a new date

DATE()

• The new date is the Year from today's date
• YEAR(TODAY())
• The month of the new date is 4 (you can change this to whatever month you need)
• The day of the new date is 1
• (i.e. April 1 2023 for any date that is April 2023 to December 2023).

If the month of today is not equal or less than 4, it creates a different new date

DATE()

• The new date is the Year from today's date minus 1 year
• YEAR(TODAY())-1
• The month of the new date is 4 (you can change this to whatever month you need)
• The day of the new date is 1
• (i.e. April 1 2023 for any date that is January 2024 to March 2024).

I'll be back with steps 3 and 4 in a second...

KPH

Step 3 - Create a sheet summary field to hold the current FY start date

You can add the formula to a new field in the sheet summary and then reference it in your formula.

If you aren't familiar with sheet summaries, you can use the icon on the right to create a summary field:

Create a new field and make it a Date format and give it a name:

Enter the formula from step 2

This field will now automatically update to show the start of the fiscal year based on today's date.

Step 4 - Use the date in the sheet summary in the count formula

This was the step 1 formula. We need to replace the part in bold.

=COUNTIFS([Completion Date]:[Completion Date], >=DATE(2024, 4, 1), [Completion Date]:[Completion Date], <=(DATE(2025, 3, 31)))

We can do that like this:

=COUNTIFS([Completion Date]:[Completion Date], >=[Current FY Start]#, [Completion Date]:[Completion Date], <=(DATE(2025, 3, 31)))

The sheet summary field name is entered like a column name but with a hash at the end to signify it is a sheet summary field.

Step 5 - Formula to work out the end of the current fiscal year (from today's date)

You can create an end date in the same way as the start date in step 2.

The formula would look something like this:

=IF(MONTH(TODAY()) >= 4, DATE(YEAR(TODAY())+1, 3, 31), DATE(YEAR(TODAY()), 3, 31))

Step 6 - Add Current FY End Date to Sheet Summary

Repeat step 3 but using the formula in step 5.

Step 7 - Add Current FY End Date from Sheet Summary to Count Formula

Repeat step 4 but using the field from step 6.

Does that all make sense and do what you need?

KPH

Hi @Samantha Baruah

So you have a column with completion date and you need to count how rows have completion dates that fall into the current FY.

You can use a COUNTIFS function with the two criteria being the start and end of the fiscal year. And then a couple of IF formula to work out the start and end of the fiscal year.

Step 1 - Formula to count rows between two dates

To start with you need a COUNTIFS that would look something like this:

=COUNTIFS([Completion Date]:[Completion Date], >=DATE(2024, 4, 1), [Completion Date]:[Completion Date], <=(DATE(2025, 3, 31)))

I have used Completion Date as the name of the completion date column and a fiscal year starting April 1, 2024, ending Mar 31, 2024. So the formula is counting all the rows where the completion date is on or after April 1 2024 and also on of before Mar 31 2025.

Step 2 - Formula to work out the start of the current fiscal year (from today's date)

To make this work with the current year, instead of putting a fixed date in as the start and end dates you can use the TODAY function to calculate these dates based on today's date.

The formula would look something like this:

=IF(MONTH(TODAY()) >= 4, DATE(YEAR(TODAY()), 4, 1), DATE(YEAR(TODAY()) - 1, 4, 1))

I am again using April 1st as the start of the year and assuming anything from April 1st, 2023 is FY 2024, from April 1st 2024, is FY 2025. You can change these dates.

The formula basically looks at todays date TODAY()

Extracts just the month

MONTH(TODAY())

If this is equal to or less than 4, then it creates a new date

DATE()

• The new date is the Year from today's date
• YEAR(TODAY())
• The month of the new date is 4 (you can change this to whatever month you need)
• The day of the new date is 1
• (i.e. April 1 2023 for any date that is April 2023 to December 2023).

If the month of today is not equal or less than 4, it creates a different new date

DATE()

• The new date is the Year from today's date minus 1 year
• YEAR(TODAY())-1
• The month of the new date is 4 (you can change this to whatever month you need)
• The day of the new date is 1
• (i.e. April 1 2023 for any date that is January 2024 to March 2024).

I'll be back with steps 3 and 4 in a second...

KPH

Step 3 - Create a sheet summary field to hold the current FY start date

You can add the formula to a new field in the sheet summary and then reference it in your formula.

If you aren't familiar with sheet summaries, you can use the icon on the right to create a summary field:

Create a new field and make it a Date format and give it a name:

Enter the formula from step 2

This field will now automatically update to show the start of the fiscal year based on today's date.

Step 4 - Use the date in the sheet summary in the count formula

This was the step 1 formula. We need to replace the part in bold.

=COUNTIFS([Completion Date]:[Completion Date], >=DATE(2024, 4, 1), [Completion Date]:[Completion Date], <=(DATE(2025, 3, 31)))

We can do that like this:

=COUNTIFS([Completion Date]:[Completion Date], >=[Current FY Start]#, [Completion Date]:[Completion Date], <=(DATE(2025, 3, 31)))

The sheet summary field name is entered like a column name but with a hash at the end to signify it is a sheet summary field.

Step 5 - Formula to work out the end of the current fiscal year (from today's date)

You can create an end date in the same way as the start date in step 2.

The formula would look something like this:

=IF(MONTH(TODAY()) >= 4, DATE(YEAR(TODAY())+1, 3, 31), DATE(YEAR(TODAY()), 3, 31))

Step 6 - Add Current FY End Date to Sheet Summary

Repeat step 3 but using the formula in step 5.

Step 7 - Add Current FY End Date from Sheet Summary to Count Formula

Repeat step 4 but using the field from step 6.

Does that all make sense and do what you need?

Samantha Baruah

@KPH : You must have been a teacher at some point in your life. This is amazing. I had worked out the formula (finally) late last night, but they way you've laid it out is so incredibly helpful and I love love your idea of adding the formula to the sheet summary. Make the cell / column formula look so much cleaner.

Thank you so very much for your help!

KPH

🤣 I did use to teach! And am a big fan of providing tools not answers. More effort in the short term for greater long term benefits.

Thank you for your kind feedback. I’m glad you have it sorted.

Samantha Baruah

@KPH : Former education person myself :)

Just the way you laid it out is so incredibly help as it provides the framework and explanation than just an answer. You made it not only so easy to follow, but because you're providing the framework, I can incorporate this knowledge elsewhere.

Thank you! Thank you!