The dataset used for these methods contains three columns with the product ID, prices of the products, and delivery dates of a company’s products.

**SUMIF Function between Two Values in Excel: An Improvisation**

**Steps:**

- Enter the following formula in
**Cell F5**:

`=SUMIF(D5:D10,{">=05-12-21",">10-12-21"},C5:C10)`

- Press
**Enter**.

As the above formula works as an array formula, there are two outputs in two cells, **F5** & **G5**.

- Subtract the outputs. Type
in`=F5-G5`

**Cell G6**.

Or, you can generate the subtraction by entering (**=**) equal sign in Cell F6, drift and click the mouse on**Cell F5**, type (**–**) minus sign, then drift and click on**Cell G5**. - Press
**Enter**.

Finally, we have generated the results.

**Method 1 – Using SUMIFS Between Two Values in Excel (Alternative to SUMIF Function)**

**1.1 With Numbers**

**Steps:**

- Enter the following formula in
**Cell G5:**

`=SUMIFS(C5:C10,C5:C10,">500",C5:C10,"<700")`

- Press
**Enter**.

The formula looks for price values **greater than 500** and **less than 700**. This brings out two values, **590 and 660****. **The result is **$1250**.

** How Does the Formula Work?**

The formula takes the criteria of two numbers, **500** and **700.** To indicate greater or less, it used the signs “**>**” and “**<**” respectively before the numbers.

For the sum, the range is C5:C10, which contains the price of the products. Here, the sum and criteria range are the same.

**1.2 With Cell References**

**Steps:**

- Enter the numbers
**500**and**700**in two different cells. We have written them in**Cell****G5**and**Cell H5.** - Enter the formula of the
**SUMIFS**function below in**Cell I5:**

`=SUMIFS(C5:C10,C5:C10,">"&G5,C5:C10,"<"&H5)`

- Press
**Enter**.

Now, if you want to copy the result to a different worksheet, the result might get manipulated. To keep the formula and result intact:

- Enter the following formula:

`=SUMIFS($C$5:$C$10,$C$5:$C$10,">"&$G$5,$C$5:$C$10,"<"&$H$5)`

** How the Formula Works**

The formula takes the criteria of two cell references, G5 and H5, for the numbers 500 and 700. To indicate greater or less, it used the signs “**>**” and “**<**” respectively before the numbers. You can see “**&**” and sign after the operators to add the cell references along with the operators.

For the sum, the range is C5:C10, which contains the price of the products. Here, the sum and criteria range are the same.

**1.3 With Named Range**

**Steps:**

**Select**the price data.

- From the
**Formulas**tab, select**Define Name,**which you can find in the drop-down menu of**Defined Names.**

- A small box will come up. There you have to do the following things:
- In the
**Name:**section, write**“****Price****”.**You can write any name of your choice. - In the
**Scope:**section, write**Workbook**( by default) - Check the range and worksheet name in the
**Refers to**section.

- In the
- Click
**OK.**

You will see the name **Price** by selecting the price data at the left corner of the worksheet. It is beside the **formula bar.**

- Enter the following formula in
**Cell F5:**

`=SUMIFS($C$5:$C$10,$C$5:$C$10,">"&$G$5,$C$5:$C$10,"<"&$H$5)`

- Press
**Enter**to see the result.

Here, you can select the range, and it will show the name Price instead of a cell reference. Again, you can simply write Price in the formula, and it will refer to the particular range **C5:C10.**

** How the Formula Works**

The formula takes the criteria of two numbers, **500** and **700.** To indicate greater or less, it used the signs “**>**” and “**<**” respectively before the numbers.

For the sum, the range is C5:C10, which contains the price of the products. Here, the sum and criteria range are the same. Writing the price in the formula will directly select this range.

**1.4 With Date Values**

**Steps:**

- Enter the following formula in
**Cell F5:**

`=SUMIFS(C5:C10,D5:D10,">=05-12-21",D5:D10,"<=10-12-21")`

- Press
**Enter**to get the result.

Here, we have set the criteria for the price having a delivery date on/after **05-12-21** and on /before **10-12-21.**

** How the Formula Works**

The formula takes the criteria of two dates, **05-12-21** and **10-12-21.** To indicate greater or less, including the dates, it used the signs “>=” and “<=” respectively before the numbers.

For the sum, the range is C5:C10, which contains the price of the products. The criteria range is **D5:D10.**

**1.5 With Cell References**

**Steps:**

- Enter the dates
**05-12-21**and**10-12-21**in**Cell F5**and**Cell G5**. - Enter the formula in
**Cell H5:**

`=SUMIFS(C5:C10,D5:D10,">="&F5,D5:D10,"<="&G5)`

- Press
**Enter.**

Use absolute cell references so that the formula and result remain the same while copying and pasting. For this, the formula becomes:

`=SUMIFS($C$5:$C$10,$D$5:$D$10,">="&$F$5,$D$5:$D$10,"<="&$G$5)`

** How the Formula Works**

The formula takes the criteria of two cell references, F5 and G5, for the dates 05-12-21 and 10-12-21. To indicate greater or less, including the dates, it used the signs “**>=**” and “**<=**” respectively before the numbers. You can see “**&**” and sign after the operators to add the cell references along with the operators.

For the sum, the range is C5:C10, which contains the price of the products. The criteria range is **D5:D10.**

**1.6 Using the TODAY Function**

**Steps:**

- Enter the formula
in`=TODAY()`

**Cell G5.** - Enter
**10**in cell**H5.** - Enter the following formula in
**Cell I5:**

`=SUMIFS(C5:C10,D5:D10,">"&TODAY(),D5:D10,"<="&TODAY()+H5)`

- Press
**Enter.**

For safety, you can use absolute references. In that case, the formula will be:

`=SUMIFS($C$5:$C$10,$D$5:$D$10,">"&TODAY(),$D$5:$D$10,"<="&TODAY()+$H$5)`

** How the Formula Works**

The formula with the **TODAY** function gives the present date.

The SUMIFS formula takes ranges for the sum as **C5:C10** and criteria **D5:D10****.**

It takes the **TODAY()** formula with operator “**>**” to indicate the dates after today. The operator concatenates with the formula by the “**&**” symbol.

The second **TODAY()** formula is added with **10** using the cell reference **H5****.** This is concatenated with the operator “<=” with the “&” sign to indicate the dates less than or equal to 10 days, including today.

**Method 2 – Applying Excel SUMIFS between Two Values with Multiple Criteria**

**Steps:**

- Enter the dates
**10-12-21**and**20-12-21**in**Cell G5**and**Cell H5**. - Enter the product ID
**A_1102**in**Cell I5.** - Enter the following formula in
**Cell J5:**

`=SUMIFS(C5:C11,D5:D11,">="&G5,D5:D11,"<="&H5,B5:B11,I5)`

- Press
**Enter.**

You can use absolute cell references in the formula for ease of copy and pasting.

The formula for this:

`=SUMIFS($C$5:$C$11,$D$5:$D$11,">="&$G$5,$D$5:$D$11,"<="&$H$5,$B$5:$B$11,$I$5)`

** How the Formula Works**

The formula takes the criteria of two dates, 05-12-21 and 10-12-21, using the cell references G5 and H5. To indicate greater or less, including the dates, it used the signs “>=” and “<=” respectively before the numbers. To concatenate the operators with the dates, the “&” sign is written before the cell references.

For the criteria of Product ID, the range is selected as A5:A10 and the criteria A_1102 is set using the cell reference I5.

For the sum, the range is C5:C10, which contains the price of the products. The criteria range is **D5:D10.**

**Formula Not Working**

If your formula isn’t working or showing an error, you might need the following checklist to find the problem.

1. Check the formats of dates and numbers.

2. Use correct operators with logic.

3. Follow the formula syntax accurately.

4. Make sure all the ranges are of the same size.

**Things to Remember**

You need to write the cell references and carefully insert the operators for particular criteria. If the criteria are not set with the dataset, it will return a zero (0) as a result.

**Download the Practice Workbook**

You can download the practice workbook from here.

**<< Go Back to Excel SUMIF Function | Excel Functions | Learn Excel**