Summing Values Based On When They Occured In A Fiscal Year

Hello, Gurus!

I need a bit of help, and have attached some sample data for your reference:

On the "Summary" tab, I'm totaling the number of wellness hours (column G) an associate has used as documented on the "Occurrence" tab's column F. The problem is, our wellness hours reset at the beginning of each fiscal year. I failed to account for that, so the formula is summing ALL wellness hours from 2023 and 2024 so far meaning associates are showing negative numbers against their 32 hour full-time allotment.

I need to fix the formula so that it only totals wellness hours for the fiscal year in which it occurred (the 'Date' field on the "Occurrence" tab) as determined by the date the file is opened, i.e.: TODAY(). So when a user opens up the file "today," fiscal 2024, it only total wellness used in fiscal 2024, if that makes it any clearer. Our fiscal calendar is on the "Calendar" tab.

I appreciate everyone's time and consideration, and look forward to any help you can offer. Thanks in advance!

personally, as you have every date and a fiscal , then I would add fiscal as a helper column, into the occurrences and add it as a main header in the summary - next to where you have today() - so the date is looked
so Summary!Q2
=INDEX(Calendar!B:B,MATCH(TODAY(),Calendar!A:A,0))
and then on the occurrence have a column
column J
=INDEX(Calendar!B:B,MATCH(Occurrences!A165,Calendar!A:A,0))
now you can just sumifs() and test the column J is in current fiscal when the spreadsheet is opened

thats what i would do - then any query you can see the fiscal being used in the summary and the fiscal assigned to the occurrence

=IFERROR(VLOOKUP(A2,'Master Roster'!A:I,9,FALSE)-SUMIFS(Occurrences!F:F,Occurrences!B:B,Summary!A2,Occurrences!J:J,Summary!$Q$2),"")

only because in the past when someone comes back and queries this sort of stuff or agues about it - you have the document showing the fiscal info and not a hidden formula doing loads of calcs - especially to senior management/board or if used in any dispute , can see the "working" if you like
any just my personal view - more about company processes then strictly about excel functions

also you could just overwrite Q2 to 2023 or 2022 etc and then show results for that fiscal and save as a record for that fiscal year

You are welcome