SUMIFS Formula for Budget Statistics

cthrasher97141

Hi All!

I am trying to write a formula to calculate budget totals based on allocation total, allocation category, fiscal month, and fiscal year. I only want to capture allocation totals made for the current fiscal year, July 2023 through June 2024.

My Environment:

I have a Budget Project Sheet with a form for the user to enter Allocation Amount (TEXT), Allocation Date (Date), Allocation Category (Dropdown -single selection). The sheet also has two helper columns (Month and Year) to breakout the month and year from the Allocation date.

I have another Metrics Project Sheet that references the Budget Project Sheet to get allocations to total for each category for the current fiscal year. This sheet also has two columns (Month and Year) for the current fiscal year. I only want to sum totals for each category if the allocation date is in the current fiscal year. Here is the latest rendition of the formula I am trying, I know it is not correct but hopefully someone can help me see the error of my ways...

=SUMIFS({Budgeting Project Sheet - Amount}, CONTAINS({Budgeting Project Sheet - Month}, Month1:Month12), CONTAINS({Budgeting Project Sheet - Year}, Year1:Year12))

Thank you for any help!

Paul Newcome

Try this instead:

=SUMIFS({Budgeting Project Sheet - Amount}, {Budgeting Project Sheet - Begin Allocation Date}, AND(@cell >= DATE(VALUE(LEFT([Fiscal Year]1, 4)), 7, 1), @cell < DATE(VALUE(RIGHT([Fiscal Year]1, 4)), 7, 1)))

Paul Newcome

Are you able to provide a screenshot for context?

cthrasher97141

Paul Newcome

Try this instead:

=SUMIFS({Budgeting Project Sheet - Amount}, {Budgeting Project Sheet - Begin Allocation Date}, AND(@cell >= DATE(VALUE(LEFT([Fiscal Year]1, 4)), 7, 1), @cell < DATE(VALUE(RIGHT([Fiscal Year]1, 4)), 7, 1)))

cthrasher97141

I think that did the trick, thank you! I think I have been staring at it too long.

Paul Newcome

Happy to help. 👍️