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 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)
- 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.
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.
- 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
- How to Vlookup and Sum Across Multiple Sheets in Excel (2 Formulas)
- 3 Easy Ways to Sum Top n Values in Excel
- How to Add Specific Cells in Excel (5 Simple Ways)
- [Fixed!] Excel SUM Formula Is Not Working and Returns 0 (3 Solutions)
- How to Add Multiple Cells in Excel (6 Methods)
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)
- 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.
- Then drag the Fill Handle. In a moment, the amounts that satisfy the logical_test pop-up.
- Apply the SUM function to find out the Total Amount in cell C4.
=SUM (E8:E19)
- Press ENTER and the Total Amount will appear.
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.
- Execute the SUM function in cell C4 and then Press ENTER. The Total Amount between the dates appears.
=SUM(E8:E19)
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.