If I load the model in 2003 works fine. 2007 no go.
I have tried sumifs same thing.
Calc is always on auto.
I have a few models that use this dependency tree and the same thing happens
when they are loaded in 2007.
using shift+alt+ctrl+F9 does calc correctly but that is the same as saving.
2007 should do a full calc when F9 or auto is enabled but it appears to only
do a full calc if you use the file save as scenario.
If you use shift+ctrl+alt+F9 to calc its ok but the next time you amend the
data it fails to recalc correctly.
You are welcome to a cut down model if you wish to experiment
"Charles Williams" wrote:
I dont see anything wrong with your formula (except that since you have
Excel 2007 you could use SUMIFS instead: its much faster).
If it works when you save it sounds like you have calculation set to Manual:
make sure that you have calculation set to Automatic.
Then try Shift-Ctrl-Alt-F9 (all at the same time) (rebuilds the dependency
tree and does a full calculation)
If that does not work make sure that the cells are not formatted as Text.
Charles
__________________________________________________
Outlines for my Sessions at the Australia Excel Users Group
http://www.decisionmodels.com/OZEUC.htm
"Mrburns" wrote in message
...
This is the one of the parents in a range of cells. None of the range
will
update unless you save the file or F2 etc.
=SUMPRODUCT(--($C$142:$C$291=$B26),--($G$142:$G$291=C$7),--($F$142:$F$291))+SUMPRODUCT(--($K$142:$K$291=$B26),--($O$142:$O$291=C$7),--($N$142:$N$291))
there are an additional two cells to the right that have similar formulae
and then there is a sum on the fourth cell. This sum will not auto calc
either as the 3 preceding cells do not change.
"Charles Williams" wrote:
Works OK for me.
Can you post the formulae you are using?
Charles
__________________________________________________
Outlines for my Sessions at the Australia Excel Users Group
http://www.decisionmodels.com/OZEUC.htm
"Mrburns" wrote in message
...
In 2007 if you use a conditional sumproduct that refers to another
conditional sumproduct then the parent sumproduct will not calculate
(even
with auto on) unless you amend a condition or F2 or save as to a new
file
name.
F9 etc has no impact on the summed amount even though the child
calculated
correctly. Parent sits there and does not listen.
Have tried with threads on/ off single processor /dual.