SUMIFS Formula for Budget Statistics
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!
Best Answer
-
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)))
Answers
-
Are you able to provide a screenshot for context?
-
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)))
-
I think that did the trick, thank you! I think I have been staring at it too long.
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!