The SUMIFS function in Google Sheets is used to sum cells that meet multiple criteria. When you want to sum values between two dates, you can use SUMIFS by setting the criteria to include a range of dates.
Here's the general syntax for using SUMIFS to sum values between two dates:
=SUMIFS(sum_range, date_range, ">=start_date", date_range, "<=end_date")
Where:
sum_range is the range of cells that you want to sum.
date_range is the range of cells that contains the dates.
start_date is the start date of the period for which you want to sum values.
end_date is the end date of the period for which you want to sum values.
Both start_date and end_date should be in a date format that Google Sheets recognizes. They can be hard-coded dates (e.g., "2024-01-01"), references to cells containing dates, or date calculations.
To sum sales between two specific dates, we'll be using a dataset with dates in Column A and sales amounts in Column B. Our aim is to calculate the total sales between January 15 and February 5, 2024.
Choose an empty cell where you want the sum to be displayed. For example, click on cell C1. This is where the result of the SUMIFS formula will appear.
In cell C1, type the formula =SUMIFS(B:B, A:A, ">=2024-01-15", A:A, "<=2024-02-05"). This formula adds up all values in Column B (Sales Amount) where the corresponding date in Column A is between January 15, 2024, and February 5, 2024, inclusive.
In this example, we will sum numbers from a specific date to today. This dataset includes the number of customers served each day in Column B and the respective dates in Column A. We will sum the customers served from April 10, 2024 to the current date.
Select a blank cell for the sum, such as C1. This cell will display the total customers served.
In C1, input =SUMIFS(B:B, A:A, ">=2024-01-10", A:A, "<=" & TODAY()). This formula calculates the total number of customers served from January 10, 2024, up to and including the current date. The TODAY() function automatically updates to the current date each day.
To sum sales from a date to end of that month, we will use a a dataset that contains the number of products sold in Column B and the dates of sale in Column A. The objective is to sum the products sold from January 10 to the end of January 2024.
Choose a cell where you want the total sales to be displayed, for instance, C1. This cell will show the sum of products sold in the specified period.
Type =SUMIFS(B:B, A:A, ">=2024-05-10", A:A, "<=" & EOMONTH(DATE(2024, 5, 10), 0)) in C1. This formula sums all products sold from January 10, 2024, to January 31, 2024. The EOMONTH function is used to find the last day of January 2024.
We're using a dataset with daily revenue figures in Column B and corresponding dates in Column A. The goal is to calculate the total revenue for the current month.
Pick a cell, such as C1 to display the sum. This cell will reflect the total revenue for the entire current month.
In cell C1, type =SUMIFS(B:B, A:A, ">=" & EOMONTH(TODAY(), -1) + 1, A:A, "<=" & EOMONTH(TODAY(), 0)). This formula calculates the total revenue from the first day to the last day of the current month. EOMONTH(TODAY(), -1) + 1 computes the first day of the current month, and EOMONTH(TODAY(), 0) finds the last day of the current month.
We hope that you now have a better understanding of how to use Google Sheets SUMIFS between dates.
Use our rent reminder app to easily set up custom reminders from your spreadsheet in just a few clicks.
If you enjoyed this article, you might also like our article on how to use SUMIFS formula in Google Sheets or our article on how to use COUNTIF function in Google Sheets.
If you want to learn how to extract a domain from an email in Google Sheets, we also suggest checking out our detailed guide.
