=sum
 

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.

=sum
 

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.

=sum
 

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.

=sum
 

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.

=sum
 

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.

=sum
 

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.