View Single Post
  #4   Report Post  
Posted to microsoft.public.excel.misc
Pete_UK Pete_UK is offline
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 -