I have a formula that gathers data from another tab in Excel then checks the data against a given Monthly budget number, if the gathered data is greater than the given budget number the amount over the budget number is displayed in a cell for the particular month. Example: Budget # $100.00 - Data # 150.00 = (-50.00)


My problem is I just can't figure out how to write the formula to include the specific year to gather data from the other tab. I'm pretty sure the added syntax should be (YEAR('Prop&Equip-Maint. Mortgage'!)=($F$1) However everything I've tried don't work.

This is the current entire formula:

=IF(SUM(FILTER('Prop&Equip-Maint. Mortgage'!$F$7:$F$1000,--(MONTH('Prop&Equip-Maint. Mortgage'!$B$7:$B$1000)=(1))*(('Prop&Equip-Maint. Mortgage'!$D$7:$D$1000)="Mortgage"),0))>$C$27,$C$2 7-SUM(FILTER('Prop&Equip-Maint. Mortgage'!$F$7:$F$1000,--(MONTH('Prop&Equip-Maint. Mortgage'!$B$7:$B$1000)=(1))*(('Prop&Equip-Maint. Mortgage'!$D$7:$D$1000)="Mortgage"),0)),"")

This formula looks for a specific catagory i.e. Mortgage which is why I need the filter, however the table covers multiple years which is why the formula has to gather the data while only considerring the year seen in the cell $F$1

Can anyone help with this?