Wednesday, April 13, 2016

Sum Value in a Date Range

Sum Values with-in a Date Range

While you’re waiting for the Office 365 Launch to start, you could calculate how many units of your top product have been sold.
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 Range

The 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
    • AND
  • on or before the End date.
Here is the formula that is entered in cell D5:
=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 Total

In 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.

No comments:

Post a Comment