=sum

[Dennis Shaw]

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.

 

[Jon Peltier]

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

 

[Dennis Shaw]

lost me mate

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
_______

 

[Jon Peltier]

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

 

[Dennis Shaw]

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]

I cannot have separate columns for these amounts as the worksheet is a

government supplied file.

Sorry, sounds like your hands are tied.

- Jon