How to Sum Between Two Numbers Formula in Excel

Get FREE Advanced Excel Exercises with Solutions!

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.

sum between two numbers formula in excel


Dataset for Download


Sum Between Two Numbers Formula in Excel

In this article, we will describe two easy methods to demonstrate how to sum between two numbers formula in Excel.


Method 1: Using SUMIFS Function

The 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 criterion 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.

Steps:

  • Insert the below formula in any blank cell (i.e. C4).

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

Formula Breakdown
  • In the formula, D8:D19; is the 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.
  • Press ENTER and the sum Amount between $800-$1500 appears.

Sum between two numbers

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.

Steps:

  • 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.
  • Hit ENTER and the sum Amount within the dates appear.

Sum Total Amount to Sum Between Two Numbers Formula in Excel

Notes
  • 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.

Steps:

  • Add a Helper Column adjacent to the range. Type the below formula in cell E8.

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

Formula Breakdown
  • The IF function only offers one logical_test, in order to add two logical arguments as one logical_test, we use the AND
  • 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.
  • IF(AND(D8>800,D8<1500),D8,0); returns the Amount that satisfies the arguments.
  • Press ENTER.

Aply IF function to Sum Between Two Numbers Formula in Excel

  • Then drag the Fill Handle. In a moment, the amounts that satisfy the logical_test pop-up.

AutoFill formula to Sum Between Two Numbers Formula in Excel

  • Apply the SUM function to find out the Total Amount in cell C4.

=SUM (E8:E19)

  • Press ENTER and the Total Amount will appear.

Determine Total Amount to Sum Between Two Numbers Formula in Excel

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.

Steps:

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

Insert Dates to Sum Between Two Numbers Formula in Excel

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

=SUM(E8:E19)

Calculate Total Amount 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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Maruf Islam
Maruf Islam

MARUF ISLAM is an excellent marine engineer who loves working with Excel and diving into VBA programming. For him, programming is like a superhero tool that saves time when dealing with data, files, and the internet. His skills go beyond the basics, including ABACUS, AutoCAD, Rhinoceros, Maxsurf, and Hydromax. He got his B.Sc in Naval Architecture & Marine Engineering from BUET, and now he's switched gears, working as a content developer. In this role, he creates techy content... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo