This tutorial explores how to SUM values using dates in creative situations:
- SUM values between two dates
- SUM values by month
- SUM values based on multiple criteria
The below data set has a list of sales by DATE sold, APP sold, and REVENUE.
PROBLEM #1 SUM between two specific dates
We have a list of dates and we wish to sum all of the Revenue between each date pair.
The function we will use is SUMIFS. The structure of SUMIFS is…
=SUMIFS(sum_range, criteria_range1, criteria1, …)The SUMIFS function accepts up to 127 range/criteria pairs that can be used to filter a set of records. The resultant record list is then aggregated by the defined sum range.
NOTE: All of the defined criteria are examined with a logical AND; meaning, all of the criteria must match for the sum range to qualify for inclusion in the final sum operation.
Example: If the Color is “Red” and the Size is “Large” then Sum the Sales. There could be other “Large” items but they may not be “Red”, and vice-versa. Both criteria must be met to qualify.
In our sample file, we want to sum the Revenue if the sale date falls between the “From” date and the “To” date.
Select cell J6 and enter the following formula:
=SUMIFS($C$4:$C$33,$A$4:$A$33,”>=”&H6, $A$4:$A$33,”<=”&I6)
Use the Fill Series handle to drag the formula down to cell J8.
Remember to use the “>=” and “<=” operators if you wish to include the defined start and end dates. If you want every date between but not including the defined start and end dates, use the “<” and “>” operators.
PROBLEM #2 SUM monthly totals
The first step is to generate a list of months. Although we want the user to see the months as Jun-18, Jul-18, Aug-18, etc…, we will start by entering in the months as actual dates.
Select cell H14 and enter the following date:
6/1/2018Using the Fill Series handle, drag down to cell H17.
Because we don’t want a sequence of days, we want a sequence of months, select the Fill Series Options button and select Fill Months.
Now that we have our months, we will format the months by pressing CTRL-1 (or right-click Format Cells.) From the Category list, select Date and then select the “Mar-12” format and click OK.
The result should appear as follows.
To calculate the Revenue, we are presented with a bit of a problem. We have the start date of each month (H14:H17), but what we lack is the end dates for those months.
To solve this problem, we will use an often overlooked yet quite useful function called EOMONTH. The structure of EOMONTH is…
=EOMONTH(start_date, months)The EOMONTH function accepts a date as its first argument and then a number as it’s second argument. The number defines how may months (forward or backward in time) you want to travel. Not only does it “travel” to the requested month, but it selects the last day of the resultant month.
Example:
By using 0 (zero) as the number of months to travel, we will remain in the current month but move to the last day of that month.
Now we will create the formula using SUMIFS and the EOMONTH functions. In cell J14 enter the following formula:
=SUMIFS($C$4:$C$33,$A$4:$A$33,”>=”&H14, $A$4:$A$33,”<=”&EOMONTH(H14,0))The result for June is as follows.
Use the Fill Series handle to fill the formula from cells J14 through J17.
PROBLEM #3 SUM monthly totals by criteria
The final problem is to perform the same calculation as in Problem #2, but include an extra step where we only calculate for a month of revenue for a given App (ex: Revenue for Utility in June).
The formula is identical to the formula created to solve Problem #2, we will copy the previous solution and the necessary parameter to accout for the requested App.
Copy/paste the formula from cell J14 into cell J23. Make the below adjustment that add the restriction for App.
=SUMIFS($C$4:$C$33,$A$4:$A$33,”>=”&H23, $A$4:$A$33,”<=”&EOMONTH(H23,0),$B$4:$B$33,I23)Since I23 contains an App name, the SUM will be restricted to not only the requested month but also the requested App.
Use the Fill Series handle to fill the formula from cells J23 through J34.
Practice Workbook
Feel free to Download the Workbook HERE.
Leila Gharani
I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.
My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.
