## How can you sum values between two dates in Excel?

What if you wanted the sum for a specific month?

And what if you decided to add in multiple criteria to your existing sum?

For example get the sum by month AND division AND product?

That’s where Excel’s SUMIFS formula comes in really handy. I cover the basics of SUMIFS right here.

But in this tutorial we’re going to dig deeper and work with dates.

### Watch the steps in this video:

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

**Excel Dashboards that Inform & Impress**

LionelNovember 11, 2018 at 9:42 pmAm I missing something: =SUMIFS($C$4:$C$33,$A$4:$A$33,”>=”&H7,$A$4:$A$33,”<="&I7)? for the life of me I keep getting zero

Leila GharaniNovember 15, 2018 at 2:01 pmYour formula looks good to me. Make sure the dates are recognized as dates on your side as well and haven’t somehow switched to text.