Instead of manually coloring the cells, have another column where you enter
1 for black and 0 for red. Use conditional formatting
(http://contextures.com/xlCondFormat01.html) to color the values red if the
corresponding cell is equal to zero. Use the SUMPRODUCT worksheet function
with two ranges, one being the values and the other being the 0/1 column.
Sumproduct multiplies each pair of entries together (one value and one 0/1)
then sums these products to get an overall sum.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Dennis Shaw" wrote in message
...
lost me mate
"Jon Peltier" wrote in message
...
Use another column as the signal column. Make its value 1 if paid or zero
if unpaid. Use sumproduct using this column and the value column. Use
conditional formatting to turn the value red if the signal is zero.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Dennis Shaw" wrote in message
...
Can I ask in a formula to not add a number in an array of a certain
colour until the colour is changed. eg unpaid is in red, when paid
change to black.