How to Use SUMIF Function between Two Values in Excel

Get FREE Advanced Excel Exercises with Solutions!

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.


SUMIF Function in Excel: Syntax

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.
criteria= the condition based on which summation will take place.
sum_range= the range of data whose specific cells based on criteria will be summed up.

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.

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

A Method with SUMIF between Two Values in Excel: Dataset

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

SUMIF between Two Values in Excel: an Improvisation

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

  • Subtract the outputs. Type =F5-G5 in 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.

Result of SUMIF between Two Values in Excel


1. Using SUMIFS Between Two Values in Excel (Alternative to SUMIF Function)

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.

SUMIFS Between Two Values (Numbers): Alternative to Excel SUMIF

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.

SUMIFS Between Two Values (Cell References): Alternative to Excel SUMIF

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)

SUMIFS result for absolute cell references for two number criteria

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

SUMIFS Between Two Values (Named Range): Alternative to Excel SUMIF

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

Finding and selecting define name from formulas tab

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

Giving name and checking range for creating list name

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

List showing the name Price

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

Result of SUMIFS using Named Range process

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.

Result of writing 2 dates directly in the SUMIFS formula

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.

SUMIFS formula result for two dates using cell references

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)

Absolute cell references for two dates using SUMIFS

🔎 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 =TODAY() in 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.

Result of using TODAY formula with SUMIFS

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)

Result of using absolute references with TODAY and SUMIFS formula

🔎 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. Applying Excel SUMIFS between Two Values with Multiple Criteria

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.

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

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)

SUMIFS result for absolute cell references with two dates and another criteria

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


Download Practice Workbook

You can download the practice workbook from here.


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.

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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Syeda Fahima Nazreen
Syeda Fahima Nazreen

SYEDA FAHIMA NAZREEN is an electrical & electronics engineer who loves exploring Excel and VBA programming. To her, programming is a time-saving tool for dealing with data, files, and the internet. She's skilled in Proteus, MATLAB, Multisim, AutoCAD, Jupiter Notebook, and MS Office, going beyond the basics. With a B.Sc in Electrical & Electronic Engineering from American International University, Bangladesh, she's shifted gears and now works as a content developer. In this role, she creates techy content exclusively... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo