Excel SUMIF Not Working (9 Possible Cases with Solutions)

This image depicts 3 reasons and their solutions when the SUMIF function is not working among the 9 reasons we will discuss in this article. You can see the solutions regarding defining criteria rightly, using the comparison operator correctly, and using the SUMIFS function for multiple criteria.

Overview image of solutions when Excel SUMIF function is not working


Introduction to the SUMIF Function in Excel

The SUMIF function adds numbers in a range if they satisfy the listed criteria. The syntax of the SUMIF function is:

SUMIF(range, criteria, [sum_range])

We have summed up the salaries of a few employees who are from the Sales department.

A brief introduction and use of SUMIF function in Excel

Excel SUMIF Function Not Working: 9 Reasons with Solutions

We are using the following dataset to demonstrate the most common solutions. We have some employee information from an organization, including the name, salary, respective department, and joining date.

Dataset for showing reasons and solutions when the SUMIF function is not working

Reason 1 – Wrongly Defined Criteria Argument in SUMIF Function

If you make a mistake during defining the criteria argument in the SUMIF function, you won’t get any results.

Here we want to get the total salary of those employees who joined on the date 1-Mar-23.

  • We put the following formula in cell C19:
  • 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.

Showing wrong value because of wrongly defined criteria argument into the SUMIF function

Solution – Define Criteria Correctly

  • Here’s the first formula in cell C19 that solves the issue:
  • The date is 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. We have to insert it inside double quotations.
  • We put another formula in cell C20:
  • We put the cell reference C18 directly as a criterion, so we don’t have to worry about format.

Showing correct value after putting criteria argument correctly into the formula

Reason 2 – Wrong Use of Comparison Operators in the SUMIF Function

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 got an incorrect result:
  • We inserted the wrong comparison operator. It should be the less than operator (<).
  • The operator should reside inside double quotations alone and then the operator and the cell reference should join with an ampersand operator (&).

Showing wrong value after using comparison operator incorrectly in the SUMIF function

Solution – Use the Comparison Operator Correctly

  • We put the correct formula in cell C19:
  • This has the correct operator, the less than operator (<) inside double quotations, and joins it with a cell reference C18 via an ampersand operator (&).

Showing right value after using comparison operator correctly in the SUMIF function

Read More: [Fixed!] Excel SUMIF with Wildcard Not Working

Reason 3 – Sum Range Is in Text Format

If the sum range is in text format, the SUMIF function can’t sum them. That’s why the following formula is giving 0 in cell C19 if the sum range C5:C16 is in text format:


Showing wrong value because sum range is in text format

Solution 1 – Changing Text Format to Number Format Directly

  • 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 Convert to Number
  • Your formula is now working fine.

Solutions 2 – Using the VALUE Function

  • Create 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:
  • We put the following formula in cell C19:
  • We changed the sum range to F5:F16 from C5:C16 because they are in number format.

Showing the right value after changing the format of sum range into a number using the VALUE function

Read More: Excel SUMIFS with Not Equal to Text Criteria

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

  • We used this formula in cell C19:
  • The formula is 100% accurate, but the result doesn’t seem right. The answer should be 10:00:00 but it is showing 0.42.
  • Excel converts 1 hour to 1/24 units.  Therefore, 10 hours will equal 0.42.

Showing incorrect output because of wrong cell format

Solution – Applying the Correct Cell Format

  • Select cell C19 and right-click.
  • Select the Format Cells option to open 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 OK.
  • You’ll get your desired result.

Reason 5 – The SUMIF Function Is Not Giving a 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:
  • 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.

Summed value is not updating after updating some values in the Salary column

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.

  • Press F9 from the keyboard to recalculate the sheet and the SUMIF function will give the updated result.

Using keyboard shortcut to update the summed value

  • Alternatively, go to Formulas and select Automatic from the Calculation Options menu.
  • Whenever you update any values, the SUMIF function will give a new result.

Selecting Automatic from Calculation Options so that summed values will update automatically

Reason 6 – The 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:


Showing wrong result after using the SUMIF function for multiple criteria

Solution – Using the SUMIFS Function for Multiple Criteria

  • Put the following formula in cell C20 instead of the previous formula:
  • We first put the sum range as C5:C16. Then, we put D5:D16 as the first criteria range and C18 as the first criteria. Similarly, we put E5:E16 as the second range and C19 as the second criteria.

Showing correct result after using the SUMIFS function for multiple criteria

Reason 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 have put the following formula and received a false result:
  • We have 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.

Showing wrong result because criteria range and sum range of the SUMIF function are not of equal size

Solution – Making Both Ranges of the Same Size

  • Use this formula instead in cell C19 by putting the range and sum range arguments of equal size to get the right result:

Showing correct result after putting equal criteria range and sum range into the SUMIF function

Reason 8 – The SUMIF Function Is Not Working Because Another Workbook Is Closed

We have the same dataset in Sheet1 of another workbook titled “Another Workbook”.

Showing the same dataset in Another Workbook

And we want to get the total salary of the date 1-Mar-23 based on this dataset in our existing workbook.

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)

Showing error because Another Workbook is close

Solution – Opening the Other Workbook

  • Open “Another Workbook” and the formula will work fine.

Showing result after opening Another Workbook

Reason 9 – The Criteria String Is Longer Than 255 Characters

In cell C18, we have a string that is greater than 255 characters. So, putting the following formula in cell C19 is giving an error:


Showing an error because criteria string is greater than 255 characters

Solution – Reduce the Criteria String to 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

We want to get the total salary of the employees who joined on 1-Mar-23.

  • The first formula is:
  • Here, the –(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 formulas are identical as the first one, but we can change the argument order inside the formula:

Showing correct result when using SUMPRODUCT function instead of SUMIF function

Download the Practice Workbook

Related Articles

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

Get FREE Advanced Excel Exercises with Solutions!
Sajid Ahmed
Sajid Ahmed

Sajid Ahmed, a BSc graduate in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, assumes the position of an Excel & VBA Content Developer at ExcelDemy. A self-motivated individual, his profound interest in research and innovation aligns seamlessly with his passion for Excel. In this role, Sajid not only adeptly addresses challenges but also demonstrates enthusiasm and expertise in gracefully navigating complex situations. This underscores his steadfast commitment to consistently delivering exceptional content. His interests... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF