Database maintenance frequently requires summing up based on criteria. Summing two values based on criteria is one of them. The article will explain a method with the **SUMIF** function to sum between two values in Excel. Moreover, we will see some effective alternatives with the **SUMIFS** function.

**Table of Contents**hide

**Download Practice Workbook**

You can download the practice workbook from here.

**Introduction to SUMIF: an Overview**

**Objective:**

It adds the cells specified by a given condition or criteria.

**Syntax:**

**=SUMIF(range, criteria, [sum_range])**

**Arguments:**

**Arguments:**

** range**= the range of the data.

**= the condition based on which summation will take place.**

*criteria***= the range of data whose specific cells based on criteria will be summed up.**

*sum_range*Notice that the **SUMIF** function contains only one criterion and its range. However, you must be looking for a formula to sum if there are two values with criteria. This is not directly possible by using the **SUMIF** function. Excel has a sub-category function of the **SUMIF** for this purpose. This is called the **SUMIFS** function. It will be clear to you if you look at the formula syntax of the **SUMIFS**:

**=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2], [criteria2],…)**

Here, you can see that the **SUMIFS** function contains a set of criteria and criteria range which makes it a perfect match to a sum between two values with criteria.

**A Method with SUMIF between Two Values in Excel**

We will be using the following dataset to explain the 3 methods.

You can see the dataset contains three columns with the product ID, prices of the products, and delivery dates of the products of a company. Now, let’s jump to the methods.

**SUMIF between Two Values in Excel: an Improvisation**

**The SUMIF function** doesn’t return results for multiple criteria or a sum between two values directly as we have discussed earlier. Still, we can use SUMIF to perform the job with a bit of improvisation. Let’s see how it works.

**Steps:**

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

**More Methods with Excel SUMIFS Function instead of SUMIF for Two Values**

**1. SUMIFS Between Two Values: Alternative to Excel SUMIF**

There can be two numbers based on which you might want to sum from a range. We can perform this with 3 different methods. You can simply write the numbers with operators. You can also use cell references. Lastly, you can create a list of the range from where you want to get the output and use that in the formula. We will explain each of these methods one by one below.

**1.1 With Numbers**

The first method is based on two numbers. For this, you can write the numbers directly in the formula with mathematical operators. You need to follow the steps below to apply this method.

**Steps:**

- Write the following formula in
**Cell G5**.

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

- Press
**Enter**.

Here the formula looks for price values **greater than 500** and **less than 700**. This brings out two values **590 and 660****. **Thus 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 is the same.

**1.2 With Cell References**

Follow the steps to use cell references with the **SUMIFS** formula.

**Steps:**

- Write the numbers
**500**and**700**in two different cells. We have written them in**Cell****G5**and**Cell H5.** - Then, write the formula of the
**SUMIFS**function in**Cell I5.**

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

- After writing the formula press
**Enter**from your keyboard.

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

- you can use the following formula:

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

**🔎**** How Does the Formula Work?**

📌 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 is the same.

**1.3 With Named Range**

Furthermore, you can also create a list of the data of the price of the products. For this, you have to follow the steps below.

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

-Let the**Scope:**section be**Workbook**( by default).

-Check the range and worksheet name in the**Refers to**section. - 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.**

- Now, write 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 just 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 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 is the same. Writing price in the formula will directly select this range.

**1.4 With Date Values**

However, you can find a summation for two date values. Again, you can do this through 3 different methods. You can write the dates or you can use the cell references. Moreover, you can also use **the TODAY function** with the dates to create a new condition other than the first 2. Now let us explore each of them.

For simplification, you can write the dates for the condition with operators in the formula. Follow the steps below:

**Steps:**

- Write 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 Does the Formula Work?**

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

You can use the **cell references** to eradicate the hassle of writing dates in the formula.

Applying this will need to follow the steps below.

**Steps:**

- Write the dates
**05-12-21**and**10-12-21**in**Cell F5**and**Cell G5**respectively. - Then, you have to write the formula in
**Cell H5.**

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

- Afterward, press
**Enter.**

It is noteworthy to use absolute cell references so that the formula and result remain the same while copy 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 Does the Formula Work?**

📌 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 With TODAY Function**

Moving forward with the use of the **TODAY** function along with criteria values of dates. If you want to set criteria with today’s date you can use this method.

Suppose you want to get the sum of the price for products whose delivery date is after today’s date and within the next 10 days from today.

You can do this by following the steps given below.

**Steps:**

- Write the formula
in`=TODAY()`

**Cell G5.** - Next, write
**10**in cell**H5.** - After that, write the following formula in
**Cell I5:**

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

- Press
**Enter**after writing the formula.

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 Does 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 “**&**” sign to indicate the dates less than or equal to **10** days including today.

**2. SUMIFS between Two Values and Other Criteria: ****Alternative to Excel SUMIF**

Let us assume that we want a summation of the price of the products between **10-12-21** and **20-12-21****.** And the other criteria are the prices will be for the product ID **A_1102.**

You need to follow the steps to know how to get results for these conditions.

**Steps:**

- Write the dates
**10-12-21**and**20-12-21**in**Cell G5**and**Cell H5**respectively. - Write the product ID
**A_1102**in**Cell I5.** - Write the 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 Does the Formula Work?**

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

In case 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 put the operators for particular criteria. If the criteria are not set with the dataset, it will provide a zero (0) as result.

**Conclusion**

Finally, we hope you will find all these methods instrumental. Moreover, the workbook is there for you to download and practice yourself. If you have any questions, comments, or any kind of feedback, please let me know in the comment box. And please visit our website **ExcelDemy** to explore more.