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

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

**Step 2: **Press** ENTER, **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**.

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

**Step 2: **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.

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

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

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

**=SUM (E8:E19)**

**Â ****Step 4: **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.

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

**Step 2: **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.