How to use SUMIF with VLOOKUP based on Date Range

0
votes

I have a Google Sheets budget spreadsheet that uses SUMIF to calculate bills due between certain days of the month. I have 2 column, E and F, that have the day of the month when the bill is due and the amount due. I'm trying to figure out how to SUMIF the value if the corresponding dates fall between a specified range of days of the month.

I'm assuming this will be a combination of SUMIF and VLOOKUP, but I have no idea how to put this formula together.

I'm expecting the calculated fields to do this: if the date range is from the 4th through the 10th, add up all of the bills due from the 4th through the 10th. Then, for the 11th through 16th, add all the bills due from the 10th through the 16th, etc.

1
google-sheetssumifsgs-vlookup
Here's an example version of the sheet: docs.google.com/spreadsheets/d/… – boito

1 Answers

1
votes

You want to calculate the values in a range where there are two criteria.

Use SUMIFS

  • sum range = $K$2:$K$24 (note: absolute reference)
  • criteria range = $I$2:$I$24 (again, absolute reference)
  • criteria#1 = ">="&B4 - greater than or equal to the start value; note the operators are in quotations and the "START" cell address is added with a "&".
  • criteria#2 = "<="&C4 - less than or equal to the end value; note the operators are in quotations and the "END" cell address is added with a "&".

=sumifs($K$2:$K$24,$I$2:$I$24,">="&B4,$I$2:$I$24,"<="&C4)