With the help of Excel’s SUMIF function, users can sum a range of numbers in accordance with specific criteria. However, there are times when the SUMIF function may not work as expected, leading to incorrect results or no results at all. In this article, we will explore 9 reasons why the SUMIF function is not working in Excel and provide solutions to each of these reasons. We will discuss various potential issues and their fixes, ranging from incorrectly defined criteria arguments to problems with cell formatting. In addition, if the SUMIF function is not functioning at all, we will offer a different approach utilizing the SUMPRODUCT function. At last, you’ll get an output like the overview image below.
This image depicts 3 reasons and their solutions when the SUMIF function is not working among the 9 reasons we discussed in this article. Here you can see the solutions regarding defining criteria rightly, using the comparison operator correctly, and using the SUMIFS function for multiple criteria.
Table of Contents hide
Download Practice Workbook
Introduction to SUMIF Function in Excel
9 Reasons with Solutions When SUMIF Function Is Not Working in Excel
1. Wrongly Defined Criteria Argument in SUMIF Function
Solution: Define Criteria Correctly
2. Wrong Use of Comparison Operator in SUMIF Function
Solution: Use the Comparison Operator Correctly
3. Sum Range Is in Text Format
Solution: Changing Text Format to Number Format Directly or Using the VALUE Function
4. Wrong Cell Format Displays Incorrect Output
Solution: Applying the Correct Cell Format
5. SUMIF Function Is Not Giving New Value After Updating Sheet
Solution: Using Keyboard Shortcut or Calculation Options Menu to Recalculate Sheet
6. SUMIF Function Is Not Working for Multiple Criteria
Solution: Using SUMIFS Function for Multiple Criteria
7. Criteria Range and Sum Range Are Not Equal in Size
Solution: Making Both Ranges of the Same Size
8. SUMIF Function Is Not Working Because Another Workbook Is Close
Solution: Opening Another Workbook
9. Criteria String Is Greater Than 255 Characters
Solution: Take Criteria String Less Than 255 Characters
The SUMPRODUCT Function in Excel: an Alternative to SUMIF
Conclusion
Download Practice Workbook
You can download the practice workbook from here and practice on your own.
Solutions When SUMIF Is Not Working.xlsx
Introduction to SUMIF Function in Excel
The SUMIF function adds numbers in a range that satisfies certain criteria. The syntax of the SUMIF function is:
SUMIF(range, criteria, [sum_range])
In the following picture, you’ll get a full overview of the SUMIF function. Here we sum up the salaries of a few employees who are from the Sales department.
9 Reasons with Solutions When SUMIF Function Is Not Working in Excel
We are using the following dataset for showing you 9 reasons with solutions when the SUMIF function is not working in Excel. Here, we have some employee information from an organization. Their name, salary, respective department, and joining date can be found in this dataset.
1. Wrongly Defined Criteria Argument in SUMIF Function
If you make a mistake during defining the criteria argument in the SUMIF function, you’ll not get any results. Depending on the situation, the SUMIF function’s criteria are specified differently. Like here we want to get the total salary of those employees who joined on the date 1-Mar-23.
- So, we put the following formula in cell C19:
=SUMIF(E5:E16,1-Mar-23,C5:C16)
- We have put the range as E5:E16 that are Joining Date column and sum range as C5:C16 that are Salary These 2 arguments are correct. But as we define the date criteria argument wrongly, it is giving 0 as a result.
Solution: Define Criteria Correctly
- To solve the issue, we have put the following 2 formulas, that will give the correct result.
- We put the first formula in cell C19:
=SUMIF(E5:E16,"1-Mar-23",C5:C16)
- Now, we have put the date inside double quotations and the SUMIF function is working nicely. This happens because the SUMIF function takes the date as a text value, not a number value. So we have to insert it inside double quotations.
- We put another formula in cell C20:
=SUMIF(E5:E16,C18,C5:C16)
- Here, we put the cell reference C18 directly as a criterion. So, we don’t have to worry about format.
2. Wrong Use of Comparison Operator in SUMIF Function
When you put the comparison operator wrongly into the Excel SUMIF function, you’ll see that the function is not working properly. Suppose, we want to get the total salary of those employees who joined before the date 2-Mar-23.
- We put the following formula in cell C19 and gets an incorrect result:
=SUMIF(E5:E16,">C18",C5:C16)
- Here we made 2 mistakes. We inserted the wrong comparison operator. It should be the less than operator (<). And the operator should reside inside double quotations alone and then the operator and the cell reference should join with an ampersand operator(&).
Solution: Use the Comparison Operator Correctly
- We put the correct formula in cell C19:
=SUMIF(E5:E16,"<"&C18,C5:C16)
- Now, we put the correct operator, the less than operator (<) inside double quotations and join it with cell reference C18 with an ampersand operator(&).
3. Sum Range Is in Text Format
If the sum range is in text format, the SUMIF function can’t sum them. Because you can sum a range of numbers, not a range of text values. That’s why the following formula is giving 0 in cell C19 because the sum range C5:C16 is in text format:
=SUMIF(E5:E16,C18,C5:C16)
Solution: Changing Text Format to Number Format Directly or Using the VALUE Function
You can change the text format into number format directly or you can use the VALUE function.
- For the first solution, simply select all the cells you want to change the format.
- Click on the triangular-shaped icon at the top of the selected cells and select the Convert to Number
- Your formula is now working fine. You’ll find the whole process in the following video.
- For the second solution, we created a new column titled “Converted to Number” in column F.
- Put the following formula in cell F5 and use the Fill Handle tool for the rest of the values:
=VALUE(C5)
- As the values are now converted to number format, we put the following formula in cell C19:
=SUMIF(E5:E16,C18,F5:F16)
- We changed the sum range to F5:F16 from C5:C16 because they are in number format.
4. Wrong Cell Format Displays Incorrect Output
When you try to add up times values, the SUMIF function might not appear to be working. Here, we want to sum up the working hours of date 1-Mar-23. The working time values are in HH:MM:SS format.
- So I use the formula in cell C19:
=SUMIF(E5:E16,C18,F5:F16)
- The formula is 100% accurate, however, the result I’m obtaining doesn’t seem right. The answer should be 10:00:00 but it is showing 42. Actually, it is not wrong. Excel converts 1 hour to 1/24 units. Therefore, 10 hours will equal 0.42.
Solution: Applying the Correct Cell Format
To solve this issue, we have to change the format of cell C19 to the time format.
- Select cell C19 and Right-Click with the mouse.
- Select the Format Cells option to open the Format Cells
- In the Number tab, choose the option Time under Category. Choose 13:30:55 as Type which is our desired format and click on the OK
- You’ll get your desired result. The whole process is shown in the video.
5. SUMIF Function Is Not Giving New Value After Updating Sheet
We have inserted the following formula in cell C19 to get the total salary of employees who joined on the date 1-Mar-23:
=SUMIF(E5:E16,C18,C5:C16)
Then, we updated 2 salaries of cells C11 and C12 to new values but the summed value in cell C19 remains unchanged.
This may happen when the formula calculation is set to manual.
Solution: Using Keyboard Shortcut or Calculation Options Menu to Recalculate Sheet
We can use the keyboard shortcut or the Calculation Options menu to solve this.
- Just press F9 from the keyboard to recalculate the sheet and the SUMIF function will give the updated result.
- Or go to the Formulas Select Automatic from the Calculation Options menu.
- Now, whenever you update any values, the SUMIF function will give a new result.
6. SUMIF Function Is Not Working for Multiple Criteria
Only one condition can be used in the SUMIF function’s syntax. You can’t use this function for multiple criteria. We have 2 criteria now based on which we want to sum up the values from the Salary column. Criteria 1 is the department name Sales and Criteria 2 is the joining date. But applying the SUMIF function isn’t giving any results:
=SUMIF(D5:E16,C18&C19,C5:C16)
Solution: Using SUMIFS Function for Multiple Criteria
We can use the SUMIFS function for multiple criteria.
- Put the formula in cell C20 instead of the previous formula:
=SUMIFS(C5:C16,D5:D16,C18,E5:E16,C19)
- Here, we first put the sum range as C5:C16. Then, put D5:D16 as the first criteria range and C18 as the first criteria. Similarly put E5:E16 as the second range and C19 as the second criteria.
7. Criteria Range and Sum Range Are Not Equal in Size
The range and sum range arguments need to be the same size for the SUMIF formula to function properly; otherwise, you’ll get inaccurate results. We are putting the following formula and getting a false result:
=SUMIF(E5:E11,C18,C5:C8)
- Here, we put the range as E5:E11 and the sum range as C5:C8 which are not of equal size. That’s why it is giving wrong summed-up values.
Solution: Making Both Ranges of the Same Size
- Write this formula instead in cell C19 by putting the range and sum range arguments of equal size to get the right result:
=SUMIF(E5:E16,C18,C5:C16)
8. SUMIF Function Is Not Working Because Another Workbook Is Close
We have the same dataset in Sheet1 of another workbook titled “Another Workbook”.
And we want to get the total salary of the date 1-Mar-23 based on this dataset in our existing workbook. So, we entered the following formula and got a #VALUE! error because our “Another Workbook” is closed.
=SUMIF('F:\[Another Workbook.xlsx]Sheet1'!$E$5:$E$16,C4,'F:\[Another Workbook.xlsx]Sheet1'!$C$5:$C$16)
Solution: Opening Another Workbook
- Simply open “Another Workbook” and the formula will work fine.
9. Criteria String Is Greater Than 255 Characters
The last reason Excel SUMIF is not working is when trying to match criteria strings that are longer than 255 characters, the SUMIF function produces inaccurate results. It will give a #VALUE! error. In cell C18, we have a string that is greater than 255 characters. So, putting the following formula in cell C19 is giving the error:
=SUMIF(E5:E16,C18,C5:C16)
Solution: Take Criteria String Less Than 255 Characters
If you can, make the string shorter. Or you can use the alternative solution which we discussed below instead of the SUMIF function.
The SUMPRODUCT Function in Excel: an Alternative to SUMIF
Use this alternate solution if the SUMIF function is not functioning for any reason at all. In this case, the SUMPRODUCT function is used. We want to get the total salary of the employees who joined on 1-Mar-23. So we put 3 formulas separately using the SUMPRODUCT function. All 3 formulas are correct.
- The first formula is:
=SUMPRODUCT(C5:C16,--(E5:E16=C18))
- Here, the formula’s –(E5:E16=C18) part generates an array of 1s and 0s, one for each row where E5:E16 and C18 have the same value and one for each row where they don’t. After multiplying each value in the range C5:C16 by its corresponding value in the array, the SUMPRODUCT function returns the sum of those products.
- The second and third formula is the same as the first one. Here, we just change the orientation inside the formula.
=SUMPRODUCT(--(E5:E16=C18),C5:C16)
=SUMPRODUCT(--(C18=E5:E16),C5:C16)
Conclusion
In this article, we have discussed 9 suitable reasons with solutions when the SUMIF function is not working in Excel. We covered a number of potential concerns, as well as their fixes, from poorly stated criteria arguments to problems with cell formatting. At last, we offer a backup plan via the SUMPRODUCT function if the SUMIF function does not work anyway.
FAQs
What are the reasons for Sumif not working? ›
- SUMIF supports only one condition. ...
- Range and sum_range should be of the same size. ...
- Range and sum_range should be ranges, not arrays. ...
- SUMIF criteria syntax. ...
- SUMIF from another workbook not working. ...
- SUMIF does not recognize text case.
To sum cells that match multiple criteria, you normally use the SUMIFS function. The problem is that, just like its single-criterion counterpart, SUMIFS doesn't support a multi-column sum range. To overcome this, we write a few SUMIFS, one per each column in the sum range: SUM(SUMIFS(…), SUMIFS(…), SUMIFS(…))
How many conditions can the Sumif check for? ›You can enter up to 127 range/criteria pairs.
How do you do a Sumif with multiple criteria? ›The syntax for SUMIFS is SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...). The first three arguments are required, with additional arguments to be added depending on how many criteria are to be included. The first required argument is sum_range, which are the actual cells to sum.
Why is my sum formula not working correctly in Excel? ›The most common reason for an Excel formula not calculating is that you have inadvertently activated the Show Formulas mode in a worksheet. To get the formula to display the calculated result, just turn off the Show Formulas mode by doing one of the following: Pressing the Ctrl + ` shortcut, or.
Why is sum not working for some cells in Excel? ›Check if your workbook is in manual calculation mode. Go to tab "Formulas" on the ribbon, then press with left mouse button on the "Calculations Options" button. In this case, the setting was on "Manual", changing it back to "Automatic" makes the SUM formula work as intended again.
Why is SUMIF returning wrong value? ›The issue is that your criteria range (B3) and sum range (C3:I3) are not the same size, so your sum range is trimmed to match the size of the criteria range, effectively only summing C3. Help in Excel sort of explains this (the example used shows how the sum range increases if it is smaller than the criteria range).
Does Sumifs only work with numbers? ›range Required. The range of cells that you want evaluated by criteria. Cells in each range must be numbers or names, arrays, or references that contain numbers. Blank and text values are ignored.
What does Sumifs return if a match Cannot be found? ›SUMIFS in excel returns 0 when it does not find the value.
How many arguments can Sumifs have? ›How many arguments does the Sumifs function have in Excel? The SUMIFS Excel function can handle upto 127 pairs of Criteria Range & Criteria Arguments.
What is the maximum range for Sumif? ›
The SUMIFS Function in Excel allows us to enter up to 127 range/criteria pairs for this formula. Remember: SUMIFS will return a numeric value.
What is the difference between Sumif and Sumifs? ›The only difference between Excel SUMIFS & SUMIF functions is that SUMIFs can check for multiple criteria at once, while SUMIF can check for one criterion at a time. The SUMIF formula returns the sum of cells based on one criterion (a result that matches one condition).
What is the advanced Sumifs function in Excel? ›What Is SUMIFS in Excel? SUMIFS in Excel is an aggregation method in Excel that comes with advanced logical and conditional parameters that allow users to apply custom logic to the business data and summarise the selected data based on conditions in real-time.
How to use SUMIFS function in Excel with multiple criteria in same column? ›To sum with more criteria, you just need to add the criteria into the braces, such as =SUM(SUMIF(A2:A10, {"KTE","KTO","KTW","Office Tab"}, B2:B10)). 3. This formula only can use when the range cells that you want to apply the criteria against in a same column.
How to use SUMIF with multiple criteria in different columns? ›If you want to sum values with multiple criteria in different columns, you can use the SUMIF function to solve this task quickly. The generic syntax is: =SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
Why is Excel counting and not summing? ›That probably means that Excel sees the values as text instead of as numbers. Try the following: Select such values in a single column. Make sure that the number format is set to General or Number.
Why does Excel show the formula and not the result? ›Excel showing formula instead of result could be an output of enabling Show Formulas feature. Chances are you didn't enable it intentionally and you are facing it accidentally. If it is so, you will not get a display of results rather you will see Excel formulas only.
Why are all my Formulas returning 0? ›This means that the cell doesn't contain any data. If the cell is blank, Excel will return a zero (0) value. You can use this feature to your advantage to make sure that cells with no data don't affect your formulas. For example, let's say you have a formula in cell A1 that multiplies the contents of cells B1 and C1.
How does Sumif function work? ›The SUMIF function is a premade function in Excel, which calculates the sum of values in a range based on a true or false condition. The condition is referred to as criteria , which can check things like: If a number is greater than another number > If a number is smaller than another number <
How do you SUM values in Excel formula? ›If you need to sum a column or row of numbers, let Excel do the math for you. Select a cell next to the numbers you want to sum, click AutoSum on the Home tab, press Enter, and you're done. When you click AutoSum, Excel automatically enters a formula (that uses the SUM function) to sum the numbers.
How do I enable SUM and count in Excel? ›
Navigate to the Home tab -> Editing group and click on the AutoSum button. You will see Excel automatically add the =SUM function and pick the range with your numbers. Just press Enter on your keyboard to see the column totaled in Excel.
Why is sumifs not returning text? ›If you are writing the correct formula and when you update sheet, the SUMIF function doesn't return updated value. It is possible that you have set formula calculation to manual. Press F9 key to recalculate the sheet.
Is there a better formula than Sumifs? ›With SUMPRODUCT
In spirit, the SUMPRODUCT option is closest to the SUMIFS formula since we are summing values based on multiple criteria. As before, it works fine as long as there is only one matching result.
However, SUMIFS easily summarizes the values horizontally and vertically individually depending on the range you specified.
What is the difference between SUMPRODUCT and Sumifs? ›The SUMPRODUCT function multiplies the range of cells or arrays and returns the sum of products. It first multiplies and then adds the values of the input arrays. This function is very similar to SUMIFS, but it is more mathematical calculation-based while SUMIFS is more logic-based.
Does Sumifs work with blanks? ›Use SUMIF to Sum Blank Values or Empty Cells
To use SUMIF to sum values for the blank criteria, you just need to specify a blank value in the criteria argument in the SUMIF function. With this, tell Excel to only consider cells where there's no value.
SUM can handle up to 255 individual arguments. The SUM function takes multiple arguments in the form number1, number2, number3, etc. up to 255 total.
What is the range of Sumifs in Excel? ›To sum values within a certain date range, use a SUMIFS formula with start and end dates as criteria. The syntax of the SUMIFS function requires that you first specify the values to add up (sum_range), and then provide range/criteria pairs. In our case, the range (a list of dates) will be the same for both criteria.
What is the maximum if conditions in Excel? ›Microsoft Excel has limits to the levels of nested IFs. In Excel 2003 and lower, up to 7 levels were allowed. In Excel 2007 and higher, you can nest up to 64 IF functions in one formula.
Can you use Sumif and VLOOKUP together? ›You can use VLOOKUP and SUMIF (or SUMIFS for multiple criteria) together in Excel for various purposes—for example: VLOOKUP within SUMIF, when you need to sum values based on conditions, but you also have to lookup from another table to get the correct criteria value.
What is the difference between pivot and Sumifs? ›
SUMIFs allow you to summarise data based on a condition, while pivot tables allow you to summarise data by row and column. In this article, we will look at how to use SUMIFs and pivot tables to organise and analyse campaign data.
What is the difference between range and sum range in Sumif? ›We discussed the SUMIF syntax in detail in the previous article, and here's just a quick refresher. range - the range of cells to be evaluated by your criteria, required. criteria - the condition that must be met, required. sum_range - the cells to sum if the condition is met, optional.
How to use SUMIFS function in Excel with multiple criteria logic? ›SUMIF using multiple criteria with wildcards
To get a total for items that contain specific text anywhere in a cell, place an asterisk on both sides, e.g. "*apple*". That's how to use SUMIF in Excel with multiple conditions.
It takes the form =SUMIF(range, criteria, [sum_range]) where Range is the range of cells you want evaluated, Criteria defines which cells are to be included and Sum_Range is the range of values to be summed if different to the main Range.
How do you count if multiple criteria are in the same column? ›If there are more than two criteria that you want to count in one column, just use =COUNTIF(range1, criteria1) + COUNTIF(range2, criteria2) + COUNTIF(range3, criteria3)+…
How do you use Countifs for multiple criteria? ›- Step 1: document the criteria or conditions you wish to test for.
- Step 2: type “=countifs(“ and select the range you want to test the first criteria on.
- Step 3: input the test for the criteria.
- Step 4: select the second range you want to test (it can be the same range again, or a new one)
SUMIFS accepts a new column between the lookup and return columns. 5. SUMIFS returns 0 when there are no matching values.
What is the correct arguments for the SUMIF function? ›The SUMIF function syntax has the following arguments: range Required. The range of cells that you want evaluated by criteria. Cells in each range must be numbers or names, arrays, or references that contain numbers.
How to use SUMIF with text condition? ›The SUMIF function is conditional if the function used to sum the cells based on certain criteria, not the criteria can be a certain text too. For example, we want to sum up a group of cells. If the adjacent cell has a specified text, we can use the function: =SUMIF(Text Range,” Text,” cells range for sum).
Does Sumifs work across sheets? ›When the data is spread out in different worksheets in similar ranges of cells, we can add categorize the data by using the SUMIF function across multiple sheets. This can be done by nesting the SUMIF function and the INDIRECT function.
How do I refresh Sumifs? ›
Use the F9 Key
To use this solution, simply select the cell that contains the SUMIF formula and press the F9 key. This will recalculate the formula and update the result.
SUMIFS returns the sum. XLOOKUP, however, can return either the first value found (default) or the last.
Can we use VLOOKUP and Sumif together? ›You can use VLOOKUP and SUMIF (or SUMIFS for multiple criteria) together in Excel for various purposes—for example: VLOOKUP within SUMIF, when you need to sum values based on conditions, but you also have to lookup from another table to get the correct criteria value.
Does Sumif ignore blank cells? ›For a blank cell, you may use SUMIF with blank using “ “ as a criterion. However, using the SUMIF is suitable only when the cells are filled with some value as the criteria you may use operator < > that shows not equal to blank.
Which of the three arguments of a Sumif function is optional? ›Syntax. With SUMIF, the sum_range is the last and optional argument - if not defined, the values in the range argument are summed. With SUMIFS, sum_range is the first and required argument. Size of ranges.
How many arguments does the sum function require? ›The SUM function uses the following arguments: Number1 (required argument) – This is the first item that we wish to sum. Number2 (required argument) – The second item that we wish to sum. Number3 (optional argument) – This is the third item that we wish to sum.