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?

I don't have Excel available, but I'd used Defined Tables, and SUMIFS(), for this. On fly e.g.:

Define your table on sheet Mortgage as tMortgage. Let's assume the headers of Table columns are Date (column B), Mortgage (column C), and Budget (column F);
Add 2 columns as rightmost one to your table, with headers e.g. Year and Month;
Into any cell in Year and Month columns, enter the formulas
(Both columns will be filled with year and month numbers for matching Date in same row);

Now, in cell on any sheet you your formula, enter it like

Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post