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 |
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 |
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 |
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 - |
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 |
All times are GMT +1. The time now is 03:10 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com