How to Include a Date Range in SUMIFS in Google Sheets

Are you struggling with including a date range in the SUMIFS function in Google Sheets? Look no further! In this tutorial, we will explore different methods to incorporate a date range in SUMIFS, making your data analysis a breeze.

Plain Dates as Date Range Criteria in SUMIFS

Let’s start with the simplest method, using plain dates as criteria in SUMIFS. Suppose you want to calculate the sum of values in column C when the dates in column B fall between 01/07/2018 and 31/07/2018. Here’s the formula:

=SUMIFS(C2:C, B2:B, ">=DATE(2018,7,1)", B2:B, "<=DATE(2018,7,31)) // returns 325

Alternatively, you can use the ISBETWEEN function instead of comparison operators:

=SUMIFS(C2:C, ISBETWEEN(B2:B, DATE(2018,7,1), DATE(2018,7,31)), TRUE) // returns 325

Cell References as Date Range Criteria in SUMIFS

Now, let’s move on to using cell references as date range criteria. Suppose you have the start date in cell D7 and the end date in cell E7. The formula would look like this:

=SUMIFS(C2:C, B2:B, ">=D7", B2:B, "<=E7) // returns 325
=SUMIFS(C2:C, ISBETWEEN(B2:B, D7, E7), TRUE) // returns 325

How to Use Date Functions in SUMIFS as Criteria

To make your formulas even more dynamic, let’s utilize date functions in SUMIFS. We’ll explore two functions – TODAY and EOMONTH.

SUMIFS to Sum a Column Based on the Dates Falling in the Current Month

To sum a column based on the dates falling in the current month, we need to find the starting and end dates dynamically. Here’s how you can do it:

To find the starting date of the current month, use the following formula:

=EOMONTH(TODAY(), -1) + 1

To find the end date of the current month, use this formula:

=EOMONTH(TODAY(), 0)

Now, let’s incorporate these dynamic dates as criteria in SUMIFS:

=SUMIFS(C2:C, B2:B, ">=EOMONTH(TODAY(), -1) + 1", B2:B, "<=EOMONTH(TODAY(), 0)) // returns dynamic sum
=SUMIFS(C2:C, ISBETWEEN(B2:B, EOMONTH(TODAY(), -1) + 1, EOMONTH(TODAY(), 0)), TRUE) // returns dynamic sum

Note: If you’re using the sample data provided above, replace the dates in column B with the dates from the current month to avoid getting a zero result.

Lastly, let’s take a look at a formula that includes an additional condition apart from the dates:

=SUMIFS(C2:C, A2:A, "Teresa", B2:B, ">=EOMONTH(TODAY(), -1) + 1", B2:B, "<=EOMONTH(TODAY(), 0)) // returns sum with additional condition
=SUMIFS(C2:C, A2:A, "Teresa", ISBETWEEN(B2:B, EOMONTH(TODAY(), -1) + 1, EOMONTH(TODAY(), 0)), TRUE) // returns sum with additional condition

And there you have it! Now you know how to include a date range in SUMIFS in Google Sheets. Feel free to explore more formulas and enhance your data analysis skills.

For more resources and tutorials on Google Sheets, visit Crawlan.com.

Happy summing!