Sum based on colour of cells or hidden rows

[soundchaser99]

I have a few reports where I need to sum the total of column D9:D380.

However, there are some cells within that range which I have shaded grey and I don't want these included in the total, even if they have numbers in them. These grey coloured cells are also hidden when I print out the reports.

So, is there any way to either:

a) Get the total for the range excluding cells which are coloured.

b) Get the total for the range excluding cells which are hidden.

 

[DominicB]

Good evening soundchaser99

This formula will give a result ignoring hidden values :

=SUBTOTAL(109,D9:D380)

Excel doesn't have a native function for handling coloured cells. Chip Pearson offers some solutions here :

http://www.cpearson.com/excel/colors.htm

Alternately, you could download my add-in via the link below that offers custom written formulae for adding/summing/identifying coloured cells/fonts.

HTH

DominicB

 

[erik.van.geit]

Hi,

Often pearson provides nice stuff and fine explanations.
His colortricks though are slow when you work with large dataranges...

some time ago NateO learned me this

select cell B1
define a name (menu insert)
ThisColor
formula
=GET.CELL(63,!A1]

when typing in a cell
=ThisColor
you will get the color of the cell at the left

problem:
this formula does only update when sheet is calculated
you can solve this using a longer formula
=ThisColor + NOW()*0

now using an extra column with these colornumbers, you can build sumproduct or sumif formulas ...

I used this same trick within code to avoid loops
let's see if I can find a thread ... so watch my edit in a few minutes

kind regards,
Erik

EDIT (as promised)
http://www.mrexcel.com/board2/viewtopic.php?t=220295&start=11
http://www.mrexcel.com/board2/viewtopic.php?t=219084