Sum Positive Numbers Between Dates

In this post, we’ll dive into the following Excel question: How do you sum positive values between two dates? Using the SUMIFS function, we’ll walk through three exercises to build a solution step by step. By the end, you’ll have a clear understanding of how to sum data based on multiple conditions. Let’s get started!

Video

---

Step-by-step Tutorial

Let’s walk through the following exercises.

Exercise 1: Summing Positive Values

Let’s say we have the following data table (Table1):

Ultimately, we’d like to be able to enter a start date and and end date, and for a formula to compute only the positive Amounts between the specified date range. We’ll break this into small steps. The first step is to sum only the positive values in the table. To do this, we’ll use the SUMIFS function. Here’s how:

We’ll use the SUMIFS function, which has the following syntax:

=SUMIFS(sum_range, criteria_range1, criteria1)

Where:

• Sum Range: This is the column of numbers to add (e.g., the “Amount” column).
• Criteria Range 1: This is the column that contains the values we’ll compare to the criteria.
• Criteria 1: specifies the criteria.

In our case, we’ll use this formula:

=SUMIFS(Table1[Amount],Table1[Amount],">0")

This formula adds all the amounts greater than zero.:

Now that we’ve summed the positive values within the Amount column, let’s take the next step.

Exercise 2: Summing Positive Values After a Specific Date

Next, let’s add a date condition: sum positive values where the date is greater than or equal to a “From Date.” We’ll store the From Date in a cell to make it easy to change:

All we need to do is add another condition to our existing SUMIFS function. Each condition is defined with a pair of arguments, the criteria range and the criteria value. So, our second condition will define two arguments… the criteria range (the Date column) and the criteria value to identify the From date. Our updated formula looks like this:

=SUMIFS(Table1[Amount],
Table1[Amount],">0", 
Table1[Date],">="&C5)

Note the use of “>=”&C5, which encloses the comparison operator (greater than or equal to) in quotes, and then joins it with the concatenation operator (&) to the From date in C5.

This formula written in D4 sums all positive amounts where the date in the “Date” column is on or after the date in C5.

We are just about there! One more condition to go.

Exercise 3: Summing Positive Values Between Two Dates

Now, we’ll include a third condition to our formula. We store the From and To dates in cells:

Then add a third condition to our SUMIFS formula.

=SUMIFS(Table1[Amount],
Table1[Amount],">0",
Table1[Date],">="&C5, 
Table1[Date],"<="&C6)

Result:

This sums all amounts where the following three conditions are met:

• The amount is greater than zero.
• The date is on or after the “From Date” in C5.
• The date is on or before the “To Date” in D5.

Key Takeaways

The SUMIFS function is incredibly flexible for summing data based on multiple conditions. Here’s a quick recap of what we covered:

• Use SUMIFS to sum positive values with ">0".
• Add a date condition using ">=" & from_date.
• Include a second date condition using "<=" & to_date.

With these steps, you can handle a wide range of scenarios by simply adding more conditions as needed.

And that is one way to sum positive values between two dates … I hope it helps!

---

File

Feel free to download the sample file.

---

Frequently Asked Questions (FAQ)

1. Can I use SUMIFS with more than three conditions?

Yes! SUMIFS supports 60+ conditions (120+ arguments).

2. What if I want to exclude zeros instead of summing positives?

Change the criteria from ">0" to "<>0" to exclude zeros.

3. Do I need to use the concatenation operator & with cell references?

When using comparison operators (e.g., ">="), you must join them to the cell reference using concatenation of some sort, either the concatenation operator (&) or a concatenation function such as CONCAT.

4. What happens if the date columns contain blanks?

Blank date cells would be excluded from our formula.

5. Is SUMIFS case-sensitive?

No. Generally, most Excel functions are case insensitive.