Excel Formula to Sum Values between Dates

0
votes

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.

1
excelexcel-formula

1 Answers

1
votes

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")