Microsoft Office Forums Need help writing formula
 Register FAQ Search Today's Posts Mark Forums Read

#1
11-04-2023, 09:48 AM
 321Fred Windows 10 Office 2021 Novice Join Date: Nov 2023 Posts: 3
Need help writing formula

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?
#2
11-05-2023, 12:31 AM
 ArviLaanemets Windows 8 Office 2016 Expert Join Date: May 2017 Posts: 857

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);
Into any cell in Year and Month columns, enter the formulas
Code:
```= YEAR([@Date])
= MONTH([@Date])```
(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
Code:
`=IF(SUMIFS(tMortgage[Budget], tMortgage[Year],2023,tMortgage[Month],1)=0,"", MIN(\$C\$27-SUMIFS(tMortgage[Budget], tMortgage[Year],2023,tMortgage[Month],1),0))`
#3
11-05-2023, 08:14 AM
 Pecoflyer Windows 10 Office 2021 Expert Join Date: Nov 2011 Location: Brussels Belgium Posts: 2,747

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

 Thread Tools Display Modes Linear Mode

 Similar Threads Thread Thread Starter Forum Replies Last Post Joanne Excel 9 12-21-2021 01:03 AM office77 Word 2 07-24-2018 06:36 PM jennamae Excel 1 11-15-2013 08:40 PM vthomeschoolmom Word 1 01-30-2013 10:40 PM fox hunter Word 3 06-29-2012 07:48 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 04:31 PM.

 -- Default Style -- Lightweight -- New Mobile Contact Us - Privacy Statement - Top