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/2018`

Using 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 5x 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.