I want to add all cells that have a certain color of shading. For example, in
one cell, give me the sum of all cells with red shading. In another cell,
give me the sum of all cells with blue shading. This is so straightforward
that Excel must be capable of doing it, but how to tell it what I want? This
is related to the concept of conditional formatting, but kind of the reverse
of it. I am a regular Joe who is not fluent in VBA or macros. Any ideas?

What criteria was used for the shading? Was / Is it mathematically
predictable?


"IndexTurret" wrote:

I want to add all cells that have a certain color of shading. For example, in
one cell, give me the sum of all cells with red shading. In another cell,
give me the sum of all cells with blue shading. This is so straightforward
that Excel must be capable of doing it, but how to tell it what I want? This
is related to the concept of conditional formatting, but kind of the reverse
of it. I am a regular Joe who is not fluent in VBA or macros. Any ideas?

If you have Excel 2003(don't know about 2007)

You could try this alternate method which doesn't use VBA or any add-ins but
cannot return a SUM to a cell.

In 2003 you can specify the Format color to look for under
EditFindOptionsFormatFormat.

Select the color from the Patterns dialog and Find All.

In the found dialog box the first cell will be highlighted.

SHIFT + End + Down Arrow to select all found cells.

The selected cells can then be summed by right-click on Status Bar and "Sum".

Otherwise you would need VBA code.

See Chip Pearson's site for code if you want to try that........specifically his
SumByColor function.

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


Gord Dibben MS Excel MVP

On Tue, 27 Nov 2007 13:51:05 -0800, IndexTurret
wrote:

I want to add all cells that have a certain color of shading. For example, in
one cell, give me the sum of all cells with red shading. In another cell,
give me the sum of all cells with blue shading. This is so straightforward
that Excel must be capable of doing it, but how to tell it what I want? This
is related to the concept of conditional formatting, but kind of the reverse
of it. I am a regular Joe who is not fluent in VBA or macros. Any ideas?

Look he

http://xldynamic.com/source/xld.ColourCounter.html

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"IndexTurret" wrote in message ...
|I want to add all cells that have a certain color of shading. For example, in
| one cell, give me the sum of all cells with red shading. In another cell,
| give me the sum of all cells with blue shading. This is so straightforward
| that Excel must be capable of doing it, but how to tell it what I want? This
| is related to the concept of conditional formatting, but kind of the reverse
| of it. I am a regular Joe who is not fluent in VBA or macros. Any ideas?

Thank you all for your help! I will investigate each tip and probably find
something that works for me. You are truly MVPs!
---IndexTurret

"IndexTurret" wrote:

I want to add all cells that have a certain color of shading. For example, in
one cell, give me the sum of all cells with red shading. In another cell,
give me the sum of all cells with blue shading. This is so straightforward
that Excel must be capable of doing it, but how to tell it what I want? This
is related to the concept of conditional formatting, but kind of the reverse
of it. I am a regular Joe who is not fluent in VBA or macros. Any ideas?