Conditional Sum Based on Cell Background Color

[Thomas M.]

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 can't remember if I've already posted a question
about this, or if the reminder was intended to prompt me to post a question.
Either way, I can't 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've 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've 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.]

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 isn't it being recognized by Excel?

--Tom

 

[Otto Moehrbach]

Post the function. HTH Otto

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 isn't it being recognized by
Excel?

--Tom

 

[Thomas M.]

The code is not mine, so I don't 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.]

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]

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

I've 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