SUMIF Doesn't Recalc Automatically

Johan wrote about a problem he was having with the SUMIF function in his worksheet. It seems that when the data in his worksheet changes, the SUMIF function doesn't automatically update. He is, however, able to update the formula if he selects it, presses F2 (to jump into edit mode) and then press Enter.

The first, easiest, and most obvious thing to check is whether you have automatic calculation turned on. Follow these steps if you are using a version of Excel prior to Excel 2007:

1. Choose Options from the Tools menu. Excel displays the Options dialog box.
2. Make sure the Calculation tab is displayed. (Click here to see a related figure.)
3. In the Calculation area, at the top of the tab, make sure that the Automatic radio button is selected. The other radio buttons all limit, in some way, when recalculation is done.
4. Click OK.

If you are using Excel 2007 then you should follow these steps, instead:

1. Click the Office button and then click Excel Options. Excel displays the Excel Options dialog box.
2. At the left of the dialog box, click Formulas. (Click here to see a related figure.)
3. In the Calculation Options section, make sure that the Automatic radio button is selected. The other radio buttons all limit, in some way, when recalculation is done.
4. Click OK.

If this does not fix the problem, then it must be related to the actual SUMIF formula. For instance, you may think that the data you are changing in the worksheet is actually referenced in the SUMIF formula, but it may not be. (If it isn't, then changing the data won't change the formula results.) Check the formula to make sure that it covers the proper range.

Remember, as well, that if your worksheet is large and complex, and the SUMIF formula is in a cell that is above your data table, it is possible that you may need two calculation passes to update the SUMIF function. Why? Because when you calculate a worksheet, Excel basically calculates the cells from left to right and top to bottom. If you have a very large worksheet, with lots of dependent calculations, and the calculations on which everything else is dependent are at the bottom or right side of the worksheet, then you may get incorrect results for the SUMIF function. (This happens only with the most complex of worksheets.) The answer is to reorganize your worksheets so that the primary calculations are placed near the top of the worksheet and as far left as possible, and the calculations that are based on those primary calculations are placed later in the worksheet.