Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Sum of certain category per month

1

 Hi Team,

Need your assistance in the excel budget that I am making. I need to get the sum of each category on a monthly basis.

What I have:

  1. 3 sheets named as: Summary computation, Cash In, and Cash out
  2. 5 categories in Cash Out: Credit Card, Bills, Grocery, Kids, and Others

What I want to see in Summary Computation:

  • To display the total amount of of each category per month
  • example: total Credit Card amount for the month of May, total Credit Card amount for June. and all following months...
  • same formula for other categories

Formula that I have:

 =IF(MONTH(CashOut!$A$2:$A$1048576)=5,SUMIF(CashOut!$B$2:$D$1048576,SummaryComputation!A2,CashOut!$D$2:$D$1048576),0)

  • CashOut!$A$2:$A$1048576 is the date column
  • CashOut!$B$2:$D$1048576 is the range of category upto amount
  • SummaryComputation!A2 is the specific category in "Computation sheet" such as "Credit Card"
  • CashOut!$D$2:$D$1048576 is the amount 

This formula displays total amount of "Credit Crad" for whole year instead of the month of May only

Please help me to solve my problem. really need this to be done the soonest. thank you. 

excel
MS Office Version: corpuzjunel
posted by: corpuzjunel (rep: 4) on Tue May 16, 2017 at 11:49 pm
Answer
Discuss

Answers

0
Selected Answer

If you want an easy formula that achieves this result, you could use this one:

=SUMIFS(D4:D8,B4:B8,"red",A4:A8,">=1/1/2017",A4:A8,"<=1/31/2017")

Replace "red" with the desired cell reference from the Summary Computation sheet.

D4:D8 is the range to sum

B4:B8 contains the categories

A4:A8 contains the dates - this is input twice, once for the start of the month and once for the end.

">=1/1/2017" says to get the date starting on January 1, 2017

"<=1/31/2017" says to get the date ending on January 31, 2017

This trick effectively selects a specific month by limiting the date based on the start and end date of the month.

If you would like more information, check out our tutorial on the SUMIFS function.

posted by: don (rep: 1989) on Wed May 17, 2017 at 3:07 am
Discuss

Discussion

Hi Don,

Thanks for this formula. I copied this formula for the rest of the month by changing month number (from1/1/2017 to 2/1/2017... and so on...) both for start date and end date. However I wonder why it doesnt compute for the month of June, September, November, February and April.

I have checked my details and I have entries for these months of 2017. But the formula says 0. Please assist. Thank you
corpuzjunel (rep: 4) May 17, '17 at 3:50 am
Hi Don,

Apologies i forgot to amend the correct end date. for these months end date souldnt be 31st. Should be 30th or 28. Thanks a lot for your help!!!!
corpuzjunel (rep: 4) May 17, '17 at 4:15 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login