# Sum based on colour of cells or hidden rows

#### soundchaser99

##### New Member
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.

### Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
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

HTH

DominicB

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
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

Replies
15
Views
463
Replies
7
Views
313
Replies
3
Views
703
Replies
0
Views
246
Replies
8
Views
728

1,221,310
Messages
6,159,176
Members
451,543
Latest member
cesymcox

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back