SUMIF not working sometimes only!

[AntBlabby8]

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?

 

[Dufus]

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]

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]

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.