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:

1. Choose Options from the Tools menu. Excel displays the Options dialog box.
2. Make sure the Calculation tab is displayed. (See Figure 1.)



Figure 1. The Calculation tab of the Options dialog box.

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

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (3069) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: SUMIF Doesn't Recalc Automatically.