A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Discussion (Misc queries)
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

subtotal colored cells



 
 
Thread Tools Display Modes
  #1  
Old December 7th 07, 03:25 PM posted to microsoft.public.excel.misc
april
external usenet poster
 
Posts: 111
Default subtotal colored cells

i used the pearson macro to add specifically colored cells. however, i have
a large spreadsheet and must add multiple ranges so i have to go to each
range and adjust the range that i want to add. the subtotal function is
perfect for setting the range, but it adds everything in the range and i only
want to add cells with a certain fill color in that range. tried this
SumByColor(SubTotal(9,H19:H22),35,false)) but got a VALUE error. Any
suggestions?

Thanks


--
aprilshowers
Ads
  #2  
Old December 7th 07, 05:11 PM posted to microsoft.public.excel.misc
Pete_UK
external usenet poster
 
Posts: 8,856
Default subtotal colored cells

I think your formula should be:

SumByColor(H19:H22,35,false)

The first parameter is the range you want to sum, and the second
parameter is the colour number.

Hope this helps.

Pete

On Dec 7, 3:25 pm, april > wrote:
> i used the pearson macro to add specifically colored cells. however, i have
> a large spreadsheet and must add multiple ranges so i have to go to each
> range and adjust the range that i want to add. the subtotal function is
> perfect for setting the range, but it adds everything in the range and i only
> want to add cells with a certain fill color in that range. tried this
> SumByColor(SubTotal(9,H19:H22),35,false)) but got a VALUE error. Any
> suggestions?
>
> Thanks
>
> --
> aprilshowers


  #3  
Old December 7th 07, 09:43 PM posted to microsoft.public.excel.misc
april
external usenet poster
 
Posts: 111
Default subtotal colored cells

yes, that is the formula, but this formula has to change each time the range
changes. if you use Subtotal function, you get the correct range, but it
subtotals everything in the range including colored cells that i don't want
in the subtotal.
--
aprilshowers


"Pete_UK" wrote:

> I think your formula should be:
>
> SumByColor(H19:H22,35,false)
>
> The first parameter is the range you want to sum, and the second
> parameter is the colour number.
>
> Hope this helps.
>
> Pete
>
> On Dec 7, 3:25 pm, april > wrote:
> > i used the pearson macro to add specifically colored cells. however, i have
> > a large spreadsheet and must add multiple ranges so i have to go to each
> > range and adjust the range that i want to add. the subtotal function is
> > perfect for setting the range, but it adds everything in the range and i only
> > want to add cells with a certain fill color in that range. tried this
> > SumByColor(SubTotal(9,H19:H22),35,false)) but got a VALUE error. Any
> > suggestions?
> >
> > Thanks
> >
> > --
> > aprilshowers

>
>

  #4  
Old December 8th 07, 12:54 AM posted to microsoft.public.excel.misc
Pete_UK
external usenet poster
 
Posts: 8,856
Default subtotal colored cells

Yes, but Excel does not include a native formula for summing by colour
- Chip Pearson's function is set up to examine each cell in a range
and add it if the colour matches, and consequently it requires a range
and a colour number.

I'm not sure what you mean when you say "...if you use Subtotal
function, you get the correct range..." - I tend to use SUBTOTAL where
I am applying filters, and the range is fixed but the function only
takes account of visible cells. Are you applying filters and you want
to sum by colours but only on the cells which are visible?

Pete

On Dec 7, 9:43 pm, april > wrote:
> yes, that is the formula, but this formula has to change each time the range
> changes. if you use Subtotal function, you get the correct range, but it
> subtotals everything in the range including colored cells that i don't want
> in the subtotal.
> --
> aprilshowers
>
>
>
> "Pete_UK" wrote:
> > I think your formula should be:

>
> > SumByColor(H19:H22,35,false)

>
> > The first parameter is the range you want to sum, and the second
> > parameter is the colour number.

>
> > Hope this helps.

>
> > Pete

>
> > On Dec 7, 3:25 pm, april > wrote:
> > > i used the pearson macro to add specifically colored cells. however, i have
> > > a large spreadsheet and must add multiple ranges so i have to go to each
> > > range and adjust the range that i want to add. the subtotal function is
> > > perfect for setting the range, but it adds everything in the range and i only
> > > want to add cells with a certain fill color in that range. tried this
> > > SumByColor(SubTotal(9,H19:H22),35,false)) but got a VALUE error. Any
> > > suggestions?

>
> > > Thanks

>
> > > --
> > > aprilshowers- Hide quoted text -

>
> - Show quoted text -


  #5  
Old December 9th 07, 05:04 PM posted to microsoft.public.excel.misc
Chip Pearson
external usenet poster
 
Posts: 7,247
Default subtotal colored cells

You could try the following code. The first function, CellsAreVisible
returns an array of 1s and 0s indicating whether the cell is visible. The
second returns an array of 1s and 0s indicating whether the cell has a
specific colorindex (either of font or background).


Function CellsAreVisible(InRange As Range, TestRows As Boolean) As Variant

Dim Arr() As Long
Dim R As Long
Dim C As Long

ReDim Arr(1 To InRange.Rows.Count, 1 To InRange.Columns.Count)

For R = 1 To InRange.Rows.Count
For C = 1 To InRange.Columns.Count
If TestRows = True Then
If InRange(R, C).EntireRow.Hidden = True Then
Arr(R, C) = 0
Else
Arr(R, C) = 1
End If
Else
If InRange(R, C).EntireColumn.Hidden = True Then
Arr(R, C) = 0
Else
Arr(R, C) = 1
End If
End If
Next C
Next R

CellsAreVisible = Arr

End Function



Function CellHasColor(InRange As Range, ColorIndex As Long, OfText As
Boolean) As Variant

Dim Arr() As Long
Dim R As Long
Dim C As Long

ReDim Arr(1 To InRange.Rows.Count, 1 To InRange.Columns.Count)
For R = 1 To InRange.Rows.Count
For C = 1 To InRange.Columns.Count
If OfText = True Then
If InRange(R, C).Font.ColorIndex = ColorIndex Then
Arr(R, C) = 1
Else
Arr(R, C) = 0
End If
Else
If InRange(R, C).Interior.ColorIndex = ColorIndex Then
Arr(R, C) = 1
Else
Arr(R, C) = 0
End If
End If
Next C
Next R

CellHasColor = Arr

End Function

You can then multiply the arrays together and with a range of number to get
the sum of the numbers in cells that are visible and have given font color.
For example, the following formula will SUM the cells in A1:A10 that are
visible and have a background color of red.

=SUMPRODUCT(CellAreVisible(A1:A10,TRUE),CellHasCol or(A1:A10,3,FALSE),A1:A10)


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)


"april" > wrote in message
...
>i used the pearson macro to add specifically colored cells. however, i
>have
> a large spreadsheet and must add multiple ranges so i have to go to each
> range and adjust the range that i want to add. the subtotal function is
> perfect for setting the range, but it adds everything in the range and i
> only
> want to add cells with a certain fill color in that range. tried this
> SumByColor(SubTotal(9,H19:H22),35,false)) but got a VALUE error. Any
> suggestions?
>
> Thanks
>
>
> --
> aprilshowers


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
sum colored cells AOP Excel Discussion (Misc queries) 4 November 8th 07 07:25 PM
counting colored cells James P Excel Discussion (Misc queries) 2 June 14th 06 05:39 PM
Select only colored cells BBlue Excel Worksheet Functions 2 March 23rd 06 09:15 PM
count colored cells? DKY Excel Worksheet Functions 21 January 19th 06 09:47 PM
Count or sum colored cells brightgirl Excel Worksheet Functions 2 December 7th 04 03:34 PM


All times are GMT +1. The time now is 06:33 AM.


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