# SUMIF not working sometimes only!

#### AntBlabby8

##### Board Regular
I have several worksheets each with the same analysis except the product is different. But the same formulas are in all of them, in the same place. In one column, there is an IF formula that says, if in another column there a number is at a certain level, than bring in the number, otherwise put "NO RIGHTS" in the cell. Then, I have a SUMIF below the table that sums another column based on whether a result in a given row says "NO RIGHTS" in that column. On 20 of 28 sheets, the SUMIF works great. On 8 sheets, I get a WRONG result, with some numbers being left out of the sum. The formula is identical, since each sheet was based on a template. Does anyone know what would cause this or the routine to go through to check for a problem so that they all work? Otherwise, i will have to go back to a super long chain of IFs added together and might even exceed the number of arguments allowed in which case I am totally screwed! Any ideas?

### Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

#### Dufus

##### Board Regular
AntBlabby8,

I would start by finding a formula that evaluates to "NO RIGHTS" and responds to the SUMIF formula properly. Copy this formula over to a sheet where the formula doesn't respond to the SUMIF and see if it now responds to the SUMIF. If it does, your problem should be solved and the cause obvious.

Dufus

#### barry houdini

##### MrExcel MVP
Can you give an example of the SUMIF formula that isn't working?

Ensure that the text that you are searching for is identical in the If formulas and SUMIFs, no missing/extra spaces etc.

edit: another possibility is that some of your "numbers" are formatted as text, this means that they may not be included in the sum even if "NO RIGHTS" appears in the relevant cell.

For one of the numbers left out of your sum do a check. Assuming it's in cell H5 use the formula =ISNUMBER(H5) - if you get FALSE then your numbers are text formatted, you need to change them to numeric, post back if that's the case....

#### AntBlabby8

##### Board Regular
Thanks, first, for the tip about copying over a for sure working formula. Well, we tried pasting over the formulas in tables that didn't totally properly with formulas from the tables that did. That didn't help. What's intriguing is the idea that there are SPACES somehow that make it not an exact match. That sometimes happens with VLOOKUPs I know, so I usually trim them if I get error messages and that does the trick. That said, I'm not sure how there could be blank spaces because the value of the cell is NO RIGHTS but there is actually a formula in the cell and the formula is identical in all cells working and not working, so the value must be the same, that is, the letters between the " " of the IF formula. Also, because there are so many numbers, we can't figure out which cells are the ones not being totalled in. We're off by some, but enough that we can't figure out the combination that totals to the missing cells. At the same time, I am going to test the idea about text vs. number. But I have to wait until work on Tuesday! Will do, and then respond if it works! THANKS for the tip.

Replies
7
Views
102
Replies
4
Views
216
Replies
3
Views
35
Replies
3
Views
122
Replies
0
Views
59

1,170,942
Messages
5,872,857
Members
432,950
Latest member
ALeXceLBr

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back