Excell 2007 conditional SUMPRODUCT
 

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.

Excell 2007 conditional SUMPRODUCT
 

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.

Excell 2007 conditional SUMPRODUCT
 

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.

Excell 2007 conditional SUMPRODUCT
 

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.

Excell 2007 conditional SUMPRODUCT
 

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.

Excell 2007 conditional SUMPRODUCT
 

Further to my reply I decided to try the sumifs again and it works a treat,
every time the data is amended. Clearly I made an error before.

However sumifs only work in 2007 and the model needs to be opened in either
2003 or 2007 so its back to the drawing board to find out why sumproduct does
not fully calc




"Mrburns" wrote:

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.

Excell 2007 conditional SUMPRODUCT
 

You are welcome to a cut down model if you wish to experiment


if you can send me a zipped file I will take a look.

Charles
__________________________________________________
Outlines for my Sessions at the Australia Excel Users Group
http://www.decisionmodels.com/OZEUC.htm