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.

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

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

### 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**:

`=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

- Here’s the first formula in cell
**C19**that solves the issue:

`=SUMIF(E5:E16,"1-Mar-23",C5:C16)`

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

`=SUMIF(E5:E16,C18,C5:C16)`

- We put the cell reference
**C18**directly as a criterion, so we don’t have to worry about format.

### 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:

`=SUMIF(E5:E16,">C18",C5:C16)`

- 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 (&)**.

#### Solution – Use the Comparison Operator Correctly

- We put the correct formula in cell
**C19**:

`=SUMIF(E5:E16,"<"&C18,C5:C16)`

- This has the correct operator, the
**less than operator (<)**inside**double quotations,**and joins it with a cell reference**C18**via an**ampersand operator (&)**.

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

`=SUMIF(E5:E16,C18,C5:C16)`

#### 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:

`=VALUE(C5)`

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

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

`=SUMIF(E5:E16,C18,F5:F16)`

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

#### 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**:

`=SUMIF(E5:E16,C18,C5:C16)`

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

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

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

### 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:

`=SUMIF(D5:E16,C18&C19,C5:C16)`

#### Solution – Using the SUMIFS Function for Multiple Criteria

- Put the following formula in cell
**C20**instead of the previous formula:

`=SUMIFS(C5:C16,D5:D16,C18,E5:E16,C19)`

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

### 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:

`=SUMIF(E5:E11,C18,C5:C8)`

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

#### 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:

`=SUMIF(E5:E16,C18,C5:C16)`

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

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)`

#### Solution – Opening the Other Workbook

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

### 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:

`=SUMIF(E5:E16,C18,C5:C16)`

#### 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:

`=SUMPRODUCT(C5:C16,--(E5:E16=C18))`

- 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:

`=SUMPRODUCT(--(E5:E16=C18),C5:C16)`

`=SUMPRODUCT(--(C18=E5:E16),C5:C16)`

**Download the Practice Workbook**

## Related Articles

- Excel SUMIF Function for Not Equal Criteria
- How to Use Excel SUMIF with Blank Cells
- How to Use SUMIF Function to Sum Not Blank Cells in Excel

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