Imagine you run a small bakery and keep track of daily sales in an Excel workbook. Each day, you record the date in one column and the total sales amount in another. Now, you want to know how much money you made on a specific day, like Oct 15, 2022.
For this, you can use SUIMIF, and to use SUMIF to sum values based on date as criteria, you can refer to the cell where you have the date or input the date directly into the function. In SUMIFS, you have the criteria argument; in this argument, you need to specify the date you want to sum values.
This tutorial will teach us to use Excel’s SUMIF with a date.
Use SUMIF by Date in Excel
As you know, SUMIF adds values in a range that meets a specific condition. Here, we will write a formula using data in a cell to tell SUMIFS only to consider it (create a condition) and then sum values from the sum range.
Here is the SUMIF SUMIF(range, criteria, [sum_range]) syntax. You must write this formula using the same syntax.
- First, enter
=SUMIF(The cell where you want the sum. - After that, refer to the date column, range A2:A15.
- Next, in the criteria argument, refer to the cell where you have the date.
- In the last argument, refer to the sum column, range B2:B15.
- In the end, enter the closing parentheses and hit enter to get the result.
=SUMIF(A2:A15,D4,B2:B15)
The formula sums up values in column B based on a condition related to dates in column A. The range A2:A15 contains dates, and the range B2:B15 contains quantities.
The formula checks each date in A2:A15 to see if it matches the date in cell D4 (“15-Oct-22”). If a match is found, the corresponding quantity from column B is added to the total.
It finds three instances of “15-Oct-22” in column A and corresponding quantities in column B (180, 265, and 153). It then adds these quantities together (180 + 265 + 153) to get a total of 598, which is displayed in cell D5.
You can also enter the date within the function argument as a value.
=SUMIF(A2:A15,"15-Oct-2022",B2:B15)
=SUMIF(A2:A15,"15-10-2022",B2:B15)
With SUMIF, dates need to be enclosed in double quotation marks when entered directly into the formula to ensure that Excel treats them correctly as text strings.
Excel requires this formatting to distinguish dates from other types of data like numbers or cell references.
By enclosing the date in double quotation marks, you’re clearly indicating to Excel that this is the specific text you want it to match.
For example, in the formula =SUMIF(A2:A15, “15-Oct-2022”, B2:B15), the date “15-Oct-2022” is enclosed in quotation marks to make sure Excel looks for this exact text in the date range A2:A15.
Without the quotation marks, Excel might not recognize the date properly, leading to errors or incorrect results.
In the same way, you can create a valid Excel date using the DATE function. See the formula below:
=SUMIF(A2:A15,DATE(2022,10,15),B2:B15)
In this formula, the DATE function creates the specific date “15-Oct-2022” by specifying the year (2022), month (10 for October), and day (15). This helps ensure that the date format is recognized correctly by Excel.
SUM Before or After a Date using SUMIF
You can also use a logical operator with the date to sum before or after a specific date. The following example shows a greater than sign with the date.
When you hit enter, it returns 2028, which is the total of the date 15-Nov-2022.
In the same way, you can use a lower than (<) operator to sum based on dates lower than the date in cell D4.
Here are some other operators that you can use:
- Not equal to <>
- Greater than or equal to >=
- Lower than and equal to <=
Create a Date Range to Sum Values with SUMIFS
To sum values within a specific date range using the SUMIFS, you need to specify both the start and end dates as criteria.
Say you have dates in column A and quantity in column B. You want to sum the sales for the date range from “01-Oct-2022” to “30-Oct-2022”.
Here’s how you can do it:
=SUMIFS(B2:B15, A2:A15, ">=01-Oct-2022", A2:A15, "<=30-Oct-2022")
Now, the above formula checks the range of dates in cells A2 to A15 and looks for dates that fall between October 1, 2022, and October 30, 2022. Each date that fits this range adds the corresponding value from B2 to B15. This means if the date in A5 is within the specified range, the value in B5 is included in the sum.
In Simple words, this formula helps you find the total sales (or any other values) in column B during October 2022. Defining the start and end dates ensures that only the October values are included.
Using Cell References for Criteria Vs. Direct Date Input
Using cell references for criteria in the SUMIF or SUMIFS is especially helpful when working with dates because it makes your formulas more straightforward to manage and update.
Instead of typing the date directly into the formula, you put the date in a separate cell and reference that cell in your formula.
If you need to change the date, you only have to update the cell with the date, not the formula itself, reducing errors and saving time.
For example, if you want to sum sales for a specific date, you can put the date in cell D1 and use the formula =SUMIF(A2:A15, D1, B2:B15), the way we have used in our example earlier in this tutorial. If you need to change the date, update cell D1; the formula will automatically use the new date.
Other Examples of SUMIF and SUMIFS to Work with Date
Here we have a few more examples to use dates with SUMIF and SIMIFS to sum values based on the date conditions.
1. Sum Values for the Current Month
This sums up the sales in column B for dates in the current month.
=SUMIFS(B2:B15, A2:A15, ">=" & EOMONTH(TODAY(), -1) + 1, A2:A15, "<=" & EOMONTH(TODAY(), 0))
2. Sum Values for the Last 7 Days
This sums up the sales in column B for dates within the last 7 days.
=SUMIFS(B2:B15, A2:A15, ">=" & TODAY() - 7)
3. Sum Values for the Current Week
This sums up the sales in column B for dates within the current week (Monday to Sunday).
=SUMIFS(B2:B6, A2:A6, ">=" & TODAY() - WEEKDAY(TODAY(), 2) + 1, A2:A6, "<=" & TODAY() - WEEKDAY(TODAY(), 2) + 7)
Related Formulas
- Sum Greater than Values using SUMIF
- Sum Not Equal Values (SUMIFS) in Excel
- SUMIF / SUMIFS with an OR Logic in Excel
- SUMIF with Wildcard Characters in Excel
- SUMIFS Date Range (Sum Values Between Two Dates Array)
- Sum IF Cell Contains a Specific Text (SUMIF Partial Text)
- Sum Values Based on Year (SUMIF Year)
- Back to the List of Excel Formulas