How to Sum Between Two Numbers Formula in Excel

Every now and then we need to find out the sum of data for a range between two numbers in Excel. In this article, we use functions such as SUMIFS, SUM, IF, and AND to sum between two number values and dates.

Let’s have a dataset that consists of delivered products on different dates with amounts.

Dataset-How to Sum Between Two Numbers Formula in Excel

Dataset for Download

Sum Between Two Numbers Formula in Excel

Method 1: Using SUMIFS Function

Generic SUMIFS function’s syntax is

 =SUMIFS (sum_range, criteria_range1, criteria1, [range2], [criteria2], …)

sum_range; declares the range we want to sum.

criteria_range1; defines the range where the criteria exist.

Criteria 1; set the criteria we look for in criteria_range1.

Case 1: Between Two Values

The SUMIFS function allows more than one criteria to evaluate any value within a range. Suppose, from the dataset, we want the sum of Amount between two values; Lower Limit $800 and Upper Limit $1500.

Step 1: Insert the below formula in any blank cell (i.e. C4).

=SUMIFS(D8:D19,D8:D19,”>”&C2,D8:D19,”<“&C3)

In the formula,

D8:D19; is the sum_range.

 D8:D19,”>”&C2; returns the entries in sum_range greater than the cell reference C2 (i.e. $800).

Similarly,

D8:D19,”<“&C3; fetches the entries in sum_range greater than the cell reference C3 (i.e. $1500).

 Finally,

SUMIFS(D8:D19,D8:D19,”>”&C2,D8:D19,”<“&C3); adds all the returned values between the lower and upper limit.

sumif function-How to Sum Between Two Numbers Formula in Excel

Step 2: Press ENTER, the sum Amount between $800-$1500 appears.

sumif function result-How to Sum Between Two Numbers Formula in Excel

Read More: Excel Sum If a Cell Contains Criteria (5 Examples)

Case 2: Between Two Dates

In this case, we want the sum of the amount between two specific dates. Let’s say, we want the sum of Amount of delivery dates after Nov 12 and before Nov 18, 2021.

Step 1: Paste the same formula as the one we use in Case 1 in any blank cell (i.e. C4).

=SUMIFS(D8:D19,C8:C19,”>”&C2,C8:C19,”<“&C3)

The formula declares the same arguments as it does in the earlier Case 1. Just change the cells (C2 and C3) entries from Lower and Upper Limit to Starting and Ending Date respectively.

sumif function between dates-How to Sum Between Two Numbers Formula in Excel

Step 2: Hit ENTER and the sum Amount within the dates appear.

sumif function between dates result-How to Sum Between Two Numbers Formula in Excel

You can input any desired values or dates in reference cells (i.e.C3 and C4) and get the sum Amount in any desired cell (i.e.C4).

Read More: Sum Cells in Excel: Continuous, Random, With Criteria, etc.


Similar Readings


Method 2: Using SUM and IF Function

The SUM function simply sums numbers; it does not apply any kind of criteria to numbers. So, we use the IF and ADD function in a Helper Column to return amounts between two specific values.

The syntax of the SUM function is

 =SUM(number1,[number2],….)

 The syntax of the IF function is

 =IF(logical_test, [value_if_true], [value_if_false])

 The syntax of AND function is

=AND(logical1, [logical2], …)

Case 1: Between Two Values

We want to extract the sum of Amounts that are greater than $800 and less than $1500 in the dataset. In doing so, we have to add a helper column to sort out the values sitting between the range.

Step 1: Add a Helper Column adjacent to the range. Type the below formula in cell E8.

=IF(AND(D8>800,D8<1500),D8,0)

Inside the formula,

The IF function only offers one logical_test, in order to add two logical arguments as one logical_test, we use the AND function.

AND(D8>800,D8<1500); declares two logical arguments; D8 (cell reference)>$800 and D8(cell reference)<$1500.

D8; will be the value if the logical_test is True.

0; will be the value if the logical_test is False.

In the end,

IF(AND(D8>800,D8<1500),D8,0); returns the Amount that satisfies the arguments.

Helper column-How to Sum Between Two Numbers Formula in Excel

Step 2: Tab ENTER and Drag the Fill Handle. In a moment, the amounts that satisfy the logical_test popup.

if and and function helper column result

Step 3: Apply the SUM function to find out the Total Amount in cell C4.

=SUM (E8:E19)

sum function

 Step 4: Press ENTER and the Total Amount will appear.

sum function result

Read More: How to Sum Range of Cells in Row Using Excel VBA (6 Easy Methods)

Case 2: Between Two Dates

Now, we want the same sum of Amounts but not between values, between dates. Let’s say, the dates are Nov 12 and 18,2021.

Step 1: Insert a Helper Column similar to Steps 1 and 2 as described in Case 1 of this method. Then just change the cell reference entries (in C3 and C4) from Lower and Upper Limit to Starting and Ending Date respectively.

sum if function between dates

Step 2: Execute the SUM function in cell C4 and then Press ENTER. The Total Amount between the dates appears.

=SUM(E8:E19)

between dates result-How to Sum Between Two Numbers Formula in Excel

Read More: How to Sum If Cell Contains Specific Text in Excel (6 Ways)

Conclusion

In this article, we use SUMIF and a combination of SUM and IF functions to sum between two numbers; the numbers can be values or dates or anything we want. The SUMIF function does the job with ease as it incorporates more than one criterion. On the other hand, a combination of the SUM and IF functions overcome their limitations with the help of a helper column. Hope these methods quench your thirst. Comment, if you have any queries and have something to stipulate.


Related Articles

Maruf Islam

Maruf Islam

My self, Maruf Islam, an engineer and Excel & VBA Content developer on Exceldemy. I enjoy solving problems, finding workable solutions, and most of the part I really like to take on challenges. On Exceldemy I write articles discussing various way outs of Microsoft Excel's stuck ons.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo