SUMIF not working sometimes only!

AntBlabby8

Board Regular
Joined
Apr 18, 2002
Messages
190
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

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

Dufus

Board Regular
Joined
Aug 19, 2006
Messages
176
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
Joined
Mar 23, 2005
Messages
20,825
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
Joined
Apr 18, 2002
Messages
190
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.
 

Forum statistics

Threads
1,136,504
Messages
5,676,244
Members
419,616
Latest member
quickflip

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top