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.
Syntax
Here is 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_rangeis the range of cells you want to sum.date_rangeis the range of cells that contains the dates.start_dateis the start date of the period you want to sum the values for.end_dateis the end date of the period you want to sum the values for.
The start and end dates must 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.
Summing Sales Between Specific Dates
To sum sales between specific dates, we will use a dataset with dates in column A and sales amounts in column B. Our goal is to calculate the total sales between January 15th and February 5th, 2024.
1. Select the Cell for the Total Sales
Choose an empty cell where you want to display the sum. For example, click on cell C1. This is where the result of the SUMIFS formula will appear.
2. Enter the SUMIFS Formula for Sales Between Specific Dates
In cell C1, enter the formula =SUMIFS(B:B, A:A, ">=2024-01-15", A:A, "<=2024-02-05"). This formula adds up all the values in column B (sales amount) when the corresponding date in column A is between January 15th, 2024, and February 5th, 2024 (inclusive).
Summing the Number of Customers From a Specific Date to Today
In this example, we will sum the 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 10th, 2024, to the current date.
1. Choose the Cell for the Sum Result
Select an empty cell for the sum, such as C1. This cell will display the total number of customers served.
2. Insert the SUMIFS Formula for Customers Served From Specific Day to Today
In cell C1, enter =SUMIFS(B:B, A:A, ">=2024-01-10", A:A, "<=" & TODAY()). This formula calculates the total number of customers served from January 10th, 2024, to the current date. The TODAY() function automatically updates to the current date each day.
Summing Product Sales From a Date to the End of the Month
To sum product sales from a date to the end of the month, we will use a dataset that contains the number of products sold in column B and the sales dates in column A. The goal is to total the products sold from January 10th until the end of January 2024.
1. Choose the Cell to Display the Total Sales
Choose a cell where you want to display the total sales, for example, C1. This cell will show the sum of products sold within the specified period.
2. Enter the SUMIFS Formula for Sales From a Date to the End of the Month
Enter =SUMIFS(B:B, A:A, ">=2024-05-10", A:A, "<=" & EOMONTH(DATE(2024, 5, 10), 0)) in cell C1. This formula sums all the products sold from January 10th, 2024, to January 31st, 2024. The EOMONTH function is used to find the last day of January 2024.
Calculating the Total Revenue for the Current Month
We are using a dataset with daily revenue figures in column B and the corresponding dates in column A. The goal is to calculate the total revenue for the current month.
1. Select the Cell for the Total Revenue for the Current Month
Choose a cell, like C1, to display the sum. This cell will reflect the total revenue for the entire current month.
2. Use the SUMIFS Formula for Current Month Revenue
In cell C1, enter =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 calculates the first day of the current month, and EOMONTH(TODAY(), 0) finds the last day of the current month.
We hope you now have a better understanding of using Google Sheets SUMIFS between dates. Visit Crawlan.com to discover more tips and tricks on Google Sheets and other online marketing tools.
Use our rent reminder app to easily set up customized reminders from your spreadsheet with just a few clicks.
If you enjoyed this article, you might also like our article on using the SUMIFS formula in Google Sheets or our article on using the COUNTIF function in Google Sheets.
If you want to learn how to extract a domain from an email in Google Sheets, we suggest checking out our detailed guide.
Join us on Crawlan.com to stay updated with the latest tips and tricks in online marketing.
