Excel - Conditional Sum Based on Cell Background Color

Asked By Thomas M. on 13-Oct-09 05:00 PM
Excel 2007

I had an Outlook reminder (5 weeks overdue) come up today prompting me to do
some work on getting Excel to do a conditional sum based on cell background
color.  The thing is, I cannot remember if I have already posted a question
about this, or if the reminder was intended to prompt me to post a question.
Either way, I cannot find any indication that I have previously posed this
query.  So, I apologize if this is a repeat question.

I have the following numbers in A2:A8.  I have indicated the background color
for each value.

53
12    Green
42    Red
89    Green
36    Green
71
20    Red

I would like to know if there is a way to do a conditional sum based on the
background color of the cells.  So, for example, a formula that keys on
green would produce a result of 137.  I have found some information implying
that the following formula should work to sum all red cells:

=SUMPRODUCT(--(colorindex(A2:A8)=3),A2:A8)

However, that formula yields a #NAME? error, which I assume is caused by the
fact that Excel does not recognize the colorindex function.  So I suspect
that colorindex is a custom function.  My question then would be, where do I
get the colorindex function?  I suppose that a second question would be, am
I on the right track?

--Tom




Thomas M. replied on 13-Oct-09 05:06 PM
Okay, this is embarrassing, but I just discovered that I already have the
function in the ThisWorkbook code module.  So I guess the new question would
be, if I have the custom function why is not it being recognized by Excel?

--Tom
Otto Moehrbach replied on 13-Oct-09 06:57 PM
Post the function.  HTH  Otto
Thomas M. replied on 14-Oct-09 04:18 PM
The code is not mine, so I do not think that I should be posting it without
the author's permission.  However, I can give link to where I found the
code.  The complete block of code is found under Code Samples at the bottom
of the page.

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

--Tom
Thomas M. replied on 14-Oct-09 05:01 PM
I forgot to mention that I copied and pasted the code exactly as it appears
on the author's Web site.  Also, upon a closer reading of my research notes,
I see where there are instructions for creating a new code module and
pasting the code into that module, which I have also done.

--Tom
Gord Dibben replied on 14-Oct-09 06:25 PM
You still get an error?

Using Bob's function I get 62 with your formula and data example.

Alt + F11 to open the Visual Basic Editor.

CTRL + r to open Project Explorer.

Right-click on your project/workbook and Insert>Module

Paste all of Bob's code into that module.

Alt + q to return to Excel.

Enter your formula in a cell.


Gord Dibben  MS Excel MVP
Thomas M. replied on 15-Oct-09 08:22 PM
I have done all that, and it still does not work for me.  Although, I have
noticed that if I place the cursor in a blank cell and type:

=co

I see "ColorIndex" as one of the options in the list of functions that
appears.  That tells me that the VBA end of things is probably good to go
and that maybe the problem is something simple, like the manner in which I
entered the formula.  For example, with an array formula you have to enter
it with CTRL+SHIFT+ENTER.  Do I need to do something similar with this
formula?

Also, just to make sure that I have not made a syntax error in the formula,
can you post the formula that worked for you?

Thanks for your help!

--Tom