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.

**Table of Contents**Expand

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

**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 sumwithin the dates appear.*Amount*

__Notes__- You can input any desired values or dates in reference cells (i.e.
**C3**and**C4**) and get the sumin any desired cell (i.e.*Amount***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)**

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

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