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.

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.

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.

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.

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.

I cannot have separate columns for these amounts as the worksheet is a
government supplied file.

Sorry, sounds like your hands are tied.

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


"Dennis Shaw" wrote in message
...
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.