I cannot reproduce your problem in Excel 2003 or Excel 2007

Starting in A119 I have
a............3
b............4TOTAL.. 5
a........... 6
b............7
TOTAL.. 8

In B118 I have =SUMPRODUCT(--(RIGHT($A119:$A190,5)="TOTAL"),--B119:B190)
(see below) and it displays 13
In B6 I typed 13 and used conditional formatting Formula Is:=$B$6<$B$118
with a red pattern

If I alter B121 to have any value but 5, B118 is no longer 13 and B6 goes
red since it is not equal to B118.

Want to send me a file I can look at? Get my email from my website


By the way:
In =SUMPRODUCT(--(RIGHT($A119:$A190,5)="TOTAL"),--B119:B190)
there is no need for the double negation in the last argument
Use =SUMPRODUCT(--(RIGHT($A119:$A190,5)="TOTAL"),B119:B190)
The double negation is need in the first augment to convert Boolean
FALSE/TRUE to 0/1
see J.E McGimpsey at
http://mcgimpsey.com/excel/formulae/doubleneg.html


--best wishes
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"Thanks" wrote in message
...
Hello
Caclulation is set to automatic.
Cell B6 formula is =B19.
B118 =SUMPRODUCT(--(RIGHT($A119:$A190,5)="TOTAL"),--B119:B190)
Conditional formating for B6 is =B6<B118
If I make a change in B119:B190 that changes B118 to where it is no longer
=
to B19 then CF does not update until I select B6 then hit F2 to enter edit
mode (make no changes) then hit enter. Manual recalc (F9) does not update
the CF.
Any ideas?