Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 05-01-2020, 10:29 AM
pulkit pulkit is offline need excel formula using if function Windows 10 need excel formula using if function Office 2007
Novice
need excel formula using if function
 
Join Date: May 2020
Posts: 1
pulkit is on a distinguished road
Default need excel formula using if function

need excel formula using if function
i have a range of date and amount spend.


i need a formula which can auto calculate the current month spendings and auto refresh after every month.
Reply With Quote
  #2  
Old 05-01-2020, 06:12 PM
Logit Logit is offline need excel formula using if function Windows 10 need excel formula using if function Office 2007
Expert
 
Join Date: Jan 2017
Posts: 307
Logit will become famous soon enough
Default

Excel formula: Sum by month | Exceljet
Reply With Quote
  #3  
Old 05-03-2020, 08:31 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline need excel formula using if function Windows 7 64bit need excel formula using if function Office 2010
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,411
Pecoflyer is a name known to allPecoflyer is a name known to allPecoflyer is a name known to allPecoflyer is a name known to allPecoflyer is a name known to allPecoflyer is a name known to all
Default

Try a Pivot Table
Reply With Quote
  #4  
Old 05-06-2020, 01:31 AM
ArviLaanemets ArviLaanemets is offline need excel formula using if function Windows 8 need excel formula using if function Office 2016
Expert
 
Join Date: May 2017
Posts: 565
ArviLaanemets is just really niceArviLaanemets is just really niceArviLaanemets is just really niceArviLaanemets is just really nice
Default

Easiest way is to use a helper column. Attached is an example with different possible solutions:

You can define dynamic named range (dnDateMonth in my example), and use this to calculate the helper column DateMonth;
Or you can calculate month number directly in helper column - like in alternate helper column DateMonth2 - instead;
You can calculate current month like in named cell nCurrMonth, and use nCurrMonth in formula to calculate the amount for current month;
You can select the month using Data Validation List for cell nMonth, and use nMonth in formula to calculate the amount for selected month. You need a helper sheet (which can be hidden) to create a list of available months which is based on your date amount entries.
Attached Files
File Type: xlsx CurrentMonthSpending.xlsx (13.9 KB, 0 views)
Reply With Quote
  #5  
Old 05-06-2020, 01:55 AM
ArviLaanemets ArviLaanemets is offline need excel formula using if function Windows 8 need excel formula using if function Office 2016
Expert
 
Join Date: May 2017
Posts: 565
ArviLaanemets is just really niceArviLaanemets is just really niceArviLaanemets is just really niceArviLaanemets is just really nice
Default

Admins! Is it possible to delete the redundant post (this one - without attachment)

Easiest way is to use a helper column. Attached is an example with different possible solutions:

You can define dynamic named range (dnDateMonth in my example), and use this to calculate the helper column DateMonth;
Or you can calculate month number directly in helper column - like in alternate helper column DateMonth2;
You can calculate current month like in named cell nCurrMonth, and use nCurrMonth in formula to calculate the amount for current month;
You can select the month using Data Validation List for cell nMonth, and use nMonth in formula to calculate the amount for selected month. You need a helper sheet (which can be hidden) to create a list of available months which is based on your date amount entries.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
SubTotal function counts the blank cells with formula LearnerExcel Excel 3 12-29-2019 04:54 AM
Formula/function to return column names for matching values GiJoe81 Excel 1 06-27-2015 12:13 AM
need excel formula using if function Need VBA Function in Word table formula nschroeder Word VBA 6 05-13-2013 03:58 PM
need excel formula using if function Excel Function co-lay Excel 3 02-13-2012 03:46 PM
need excel formula using if function Would appreciate help with formula or function e_a_g_l_e_p_i Excel 1 03-12-2011 08:53 PM

Other Forums: Access Forums - Senior Forums

All times are GMT -7. The time now is 02:36 AM.


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