Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Junior Member
 
Posts: 27
Default sum up a column ignoring values coloured red

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?
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,872
Default sum up a column ignoring values coloured red

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
  #3   Report Post  
Junior Member
 
Posts: 27
Question

Quote:
Originally Posted by Claus Busch View Post
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!
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,872
Default sum up a column ignoring values coloured red

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
  #5   Report Post  
Junior Member
 
Posts: 27
Question

Quote:
Originally Posted by Quilp View Post
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!
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?


  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,872
Default sum up a column ignoring values coloured red

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
  #7   Report Post  
Junior Member
 
Posts: 27
Default

Quote:
Originally Posted by Claus Busch View Post
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.
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
calculate average from non contiguous column, ignoring (0) values Jesse Excel Worksheet Functions 3 June 30th 09 10:41 PM
adding values in coloured cells lois Excel Discussion (Misc queries) 2 November 5th 07 05:32 PM
average values in non-contiguous cells, ignoring 0 values RWormdahl Excel Worksheet Functions 3 October 30th 06 02:06 AM
Ignoring Negative Values solomon_monkey Excel Discussion (Misc queries) 3 June 21st 05 03:15 PM
a macro to count coloured values and copy.... dynamicsoul[_4_] Excel Programming 0 September 16th 03 03:43 PM


All times are GMT +1. The time now is 03:08 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"