It can be frustrating, even problematic, when your Excel formulas are not working.We use formulas in our Excel spreadsheets to automatically calculate values for us, and many of those formulas rely on data that is in several cells.(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. Because when you calculate a worksheet, Excel basically calculates the cells from left to right and top to bottom.That setting affects all the other workbooks the you open during that session.In this case, the workbook calculation was set to Automatic on all the machines – that’s the first thing we checked.Our guide below will show you how to get your formulas to start working by switching to automatic calculation, or by telling Excel to calculate your formulas now.
Note that your formula value should update after you click the Automatic option.
Nrobinson5 suggested that the formula uses absolute references: instead of =SUM(B8: B9), I entered formula =SUM($B:$B).
If I had used absolute references, the formula would return the same total for each salesperson, but that's not what I did.
A workbook of mine that worked fine for several years, when using Excel 2003, suddenly refused to update all the formulas, after a switch to Excel 2010. When someone tells you that formulas aren’t calculating, it’s probably because the Calculation setting has been changed to Manual, instead of Automatic.
Even more mysterious, the calculations worked fine on some machines, but not others. That can happen if the first workbook that you open in an Excel session was saved as Manual calculation.