Your question, your audience. Choose who sees your identityâ€”and your questionâ€”with question security.

I haven't the time to research this one, therefore I need help from the experts.

Creating a worksheet that calculates values both across as well as down. As an example:

| quantity | value | 1.15 | sum |

| quantity | value | 1.15 | sum |

| quantity | value | 1.15 | sum |

| quantity | value | 1.15 | sum |

| quantity | value | 1.15 | sum |

| | | |total|

The value of "sum" is cell 1*cell 2*cell 3

Initially sum calculates correctly, however if I change either the quantity of value figures, sum does not recalculate (or update).

What needs to be changed?

Dennis

Creating a worksheet that calculates values both across as well as down. As an example:

| quantity | value | 1.15 | sum |

| quantity | value | 1.15 | sum |

| quantity | value | 1.15 | sum |

| quantity | value | 1.15 | sum |

| quantity | value | 1.15 | sum |

| | | |total|

The value of "sum" is cell 1*cell 2*cell 3

Initially sum calculates correctly, however if I change either the quantity of value figures, sum does not recalculate (or update).

What needs to be changed?

Dennis

It seems like you have calculation set to manual.

Go to Tools/Options and on the tab 'Calculation', check the option 'Automatic'.

You can also hit F9 to force a manual recalculation, but if you set it to Automatic as described above, it should work.

calacuccia

Can you mail me the workbook?

calacuccia@hotmail.com

What happens if you hit CTRL+ALT+F9 ?

Usually, this solves all calculation problems or bugs (and they exist, see http://support.microsoft.com/support/kb/articles/Q171/3/39.ASP)

Shekerra, the values in the cells are set to numeric.

Cal, the article really doesn't apply here as this is Office 2000 with all of the latest updates and fixes.

When Using 'Natural Language Formulas'

http://support.microsoft.com/support/kb/articles/Q200/6/88.ASP

This article shows a method to programmatically force Excel to recalculate, using the Application.CalculateFull method.

Is your problem (the urgent one) solved now?

If you retype all (ALL) of the values (ALL), will it recalc?

If that does nothing...

Can you copy the contents and paste them into a fresh sheet?

Perhaps you have an addin that turns calculation off?

How about putting this statement at the top of VBA function:

Application.Volatile (True)

Per chance are you using autofilter?

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.

Article last modified on 11-29-2000

SYMPTOMS

========

When you create a formula in Microsoft Excel that uses the SUMIF() worksheet function, and you then change one of the values that you want to sum, the formula does not return an updated sum as expected.

CAUSE

=====

This issue can occur if the range that is evaluated for the criteria and the sum_range are not the same length, and the value that you change is outside of the range of the sum_range but inside the range of the cells being evaluated, as in the following example:

1. Start Excel, and then create the following worksheet.

A1: 3 B1: 4

A2: 3 B2: 5

A3: 3 B3: 6

A4: 3

2. In cell C4, type the following formula:

=SUMIF(A1:A3, A4, B1:B2)

With this formula, you evaluate the range of cells A1:A3 using the criteria in cell A4. The range of cells that you want to sum is specified as B1:B2, which

is a different length than the range of cells that you are evaluating. Excel automatically extends the sum_range to include B3 and returns a value of 15.

3. Change the value in cell B3 to 7, and then press F9 to recalculate.

B3 is not specified in the formula, therefore the recalculate command doesn't

work as expected.

RESOLUTION

==========

To resolve this issue, change the formula so that the criteria range and the sum_range are the same length.

In the example shown in the "Cause" section, change the formula to read as

follows:

=SUMIF(A1:A3, A4, B1:B3)

-or-

=SUMIF(A1:A2, A4, B1:B2)

WORKAROUND

==========

To work around this issue, change or delete and then re-enter a value in the specified sum_range.

In the example shown in the "Cause" section, after performing step 3, delete the value in B1, and then re-enter the value 4. The formula recalculates and then

returns the correct value of 16.

STATUS

======

Microsoft has confirmed this to be a problem in the Microsoft products that are

listed at the beginning of this article.

REFERENCES

==========

For more information about the SUMIF() worksheet function, click Microsoft Excel

2000 Help on the Help menu, type "sumif" (without the quotation marks) in the

Office Assistant or the Answer Wizard, and then click Search to view the topic.