I have a column of monetary values for which I need to find the sum total but excluding the rows where the value is coloured red. If this is not possible, can I enter the values, that I wish to ignore, in some other way e.g bold, italic etc?

Hi,

Am Mon, 15 Dec 2014 19:25:51 +0000 schrieb Quilp:

I have a column of monetary values for which I need to find the sum
total but excluding the rows where the value is coloured red. If this
is not possible, can I enter the values, that I wish to ignore, in some
other way e.g bold, italic etc?

you could use Filter by color = No fill and then
=Subtotal(9,A1:A20)
Modify range to suit.


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Thanks Claus. Could you kindly simplify your suggestion for me because I am a novice user of spreadsheets and at the moment your reply has baffled me!

Hi,

Am Tue, 16 Dec 2014 14:10:06 +0000 schrieb Quilp:

Could you kindly simplify your suggestion for me because
I am a novice user of spreadsheets and at the moment your reply has
baffled me!

use autofilter for the expected column = Filter by Color = No Fill
So you have only the cells without red fill color visible. And then
works the SUBTOTAL formula to sum these cells.
If the red color is because Conditional Formatting then please post the
condition for the CF.

If you don't want to filter or you can't filter you have to use VBA


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

I have had another thought Claus. The values I wish to ignore are ignored because they are not valid until a certain date is reached after which they can be included in the summation. Is there an alternative solution to my problem bearing this in mind?

Hi,

Am Tue, 16 Dec 2014 18:15:31 +0000 schrieb Quilp:

I have had another thought Claus. The values I wish to ignore are
ignored because they are not valid until a certain date is reached after
which they can be included in the summation. Is there an alternative
solution to my problem bearing this in mind?

if you have a date in every row you could use SUMIF.
If the date in column A is greater or equal 01.01.2015 then the values
in B will be added:
=SUMIF(A1:A200,"="&DATE(2015,1,1),B1:B200)


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Thanks Claus. I have solved my problem another way - in my confusion I "couldn't see the wood for the trees" (old English saying) I have added another column "k"showing the future date on which I want to add a value to column J, which is the one I want to SUM. Then in this column I have entered this formula which has resolved my problem:

=IF(K16(TODAY()),(G16*0.0328767%*31),0)

Thanks very much for your help.