Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: SUMIF Doesn't Recalc Automatically.

# SUMIF Doesn't Recalc Automatically

by Allen Wyatt
(last updated June 23, 2018)

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.)
3. Figure 1. The Calculation tab of the Options dialog box.

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

##### Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

##### MORE FROM ALLEN

Auto Creation of an Acronym List

If you use a lot of acronyms in your documents, you may want a quick way to compile those acronyms and their definitions ...

Discover More

Getting Rid of 'Mail To:' in E-mail Links

If you convert e-mail addresses to hyperlinks, you could end up with some 'mailto:' verbiage at the beginning of the ...

Discover More

Leaving Trace Precedents Turned On

The Trace Precedents auditing tool can be quite helpful in seeing which cells "feed into" a particular formula. The ...

Discover More

Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!

Using the XIRR Function

One of the financial worksheet functions provided in Excel is the XIRR function. This is used to figure out an internal ...

Discover More

Summing Only Visible Values

When you use SUM to determine the total of a range of values, Excel doesn't really pay attention to whether the values ...

Discover More

Rounding to Even and Odd Values

Want to round values so they are always even or odd? You can do it quickly and easily by using the EVEN and ODD worksheet ...

Discover More
##### Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. Youâ€™ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 4 + 1?

There are currently no comments for this tip. (Be the first to leave your commentâ€”just use the simple form above!)

##### This Site

Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.