Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 11-04-2023, 09:48 AM
321Fred 321Fred is offline Need help writing formula Windows 10 Need help writing formula Office 2021
Novice
Need help writing formula
 
Join Date: Nov 2023
Posts: 3
321Fred is on a distinguished road
Default 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?
Reply With Quote
  #2  
Old 11-05-2023, 12:31 AM
ArviLaanemets ArviLaanemets is offline Need help writing formula Windows 8 Need help writing formula Office 2016
Expert
 
Join Date: May 2017
Posts: 857
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

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
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))
Reply With Quote
  #3  
Old 11-05-2023, 08:14 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Need help writing formula Windows 10 Need help writing formula Office 2021
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,747
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

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
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Need help writing formula Problem writing Excel formula between two dates Joanne Excel 9 12-21-2021 01:03 AM
Writing going off to the side office77 Word 2 07-24-2018 06:36 PM
Need help writing formula formula writing for extracting data jennamae Excel 1 11-15-2013 08:40 PM
font for the formula I am writing vthomeschoolmom Word 1 01-30-2013 10:40 PM
Need help writing formula writing along lines fox hunter Word 3 06-29-2012 07:48 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 03:33 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft