Thanks so far Jon. What I am actually doing is putting figures in a column
of cells which are payments to be paid to me. If I receive the cheque with
the order, I have the numbers (text) black if no cheque the amount to be
paid is entered and are red. As I receive payment I change the amounts from
red to black. I have running total in a separate cell. I really only want
the total recieved to appear in this column. I cannot have separate columns
for these amounts as the worksheet is a government supplied file. I work as
a bailiff so doing jobs in an orderly manner is not always possible. I am
just wanting to be able to see the amount I have received at a glance.
Possibly I could dedicate a separate cell for the 'red' nubers. Hope this
makes sense.
Dennis
"Jon Peltier" wrote in message
...
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.