0

I maintain an excel budget to help keep track of my day to day expenses, and which of them relate to personal expenditure and which relate to business. I pay for everything on one credit card, so use the excel to keep track of what relates to what.

I'm trying to do a "SUMIFS" to sum values between a date range to work out the value of my credit card payment at the end of each month for personal and business expenditure, I've left the formula below and will annotate it.

=SUMIFS(I:I,B:B,">="&"10/"&MONTH(B488)-1&"/"&YEAR(B488),B:B,"<"&"10/"&MONTH(B488)&"/"&YEAR(B488),C:C,"PER")
  • Column I:I is the column with all of my expenditure on the credit card
  • Column B:B is my dates column
  • The first criteria is saying, sum between the 10th of the previous month, cell B488 is the date of my American express payment. So for example if the payment is the 24th September, this criteria says "Sum greater than the 10th August"
  • The second criteria is saying, sum between the 10th of the current month, cell B488 is the date of my American express payment. So for example if the payment is the 24th September, this criteria says "Sum less than the 10th September"
  • Column C:C is my reference column for whether an expense is personal or business
  • The last criteria is saying sum if it is a personal expense

The end result of the formula is it should sum all personal expenditure between the 10th of the previous month and 10th of the current month. However, it is returning values which are a way off.

Improve this question

1 Answer 1

Reset to default
1

Try this date construction.

=SUMIFS(I:I, B:B,">="&date(YEAR(B488), MONTH(B488)-1, 10), B:B,"<"&date(YEAR(B488), MONTH(B488), 10), C:C, "PER")
'alternate
=SUMIFS(I:I, B:B,">="&EOMONTH(B488, -2)+10, B:B,"<"&EOMONTH(B488, -1)+10, C:C, "PER")
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Thanks I'll give that a go!
Just tried, still giving a value £113 lower than it should be
Then you have some dates that are actually text that looks like a date, not a true date or PER with leading/trailing space(s). Try ... , C:C, "*PER*") first.
Apologies, I had an error in my dates like an idiot. Works like a charm. Thanks for your help.
@AdamHindle, if Jeeped solved your question, please mark his post as answered/solved by the green tick to give him the credit he deserves! :) Take care

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.