Syeda Fahima Nazreen
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.
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:
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:
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
=F5-G5in 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
=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.
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.
