If you’re using Excel 2007 and later, use the SUMIFS function , and for earlier versions, use the SUMIF function .
In this example, a Start date and an End date are entered on the worksheet. Dates are in column A, and units sold are in column B.
Use SUMIFS to Calculate Total for a Date RangeThe SUMIFS function to calculate a total based on multiple criteria. We'll use a SUMIFS formula to total all the units where the sales date is:
on or after the Start date
- on or before the End date.
=SUMIFS( $B$2:$B$9 , $A$2:$A$9 , ">=" & $D$2 , $A$2:$A$9 , "<=" & $E$2 )
- Range $B$2:$B$9 has the numbers that we want to sum.
- Range $A$2:$A$9 contains the sales dates .
- The first criterion, ">=" & $D$2 , is the range with the value for criteria 1 (the Start date), and the operator to use with that value (greater than or equal to)
- The second criterion, "<=" & $E$2 , is the range with the value for criteria 2 (the End date), and the operator to use with that value (less than or equal to)
Verify the TotalIn this example, the result for the selected date range is a total of 494 units sold. To verify, you can select cells B3:B6, and look at the total shown in Excel's Status Bar.
To get the total units for a different date range, change the Start date in cell D2, and/or the End date in cell E2.