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

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." wrote in message
...
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

Post the function. HTH Otto
"Thomas M." wrote in message
...
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." wrote in message
...
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

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

"Otto Moehrbach" wrote in message
...
Post the function. HTH Otto
"Thomas M." wrote in message
...
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." wrote in message
...
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

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

"Thomas M." wrote in message
...
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

"Otto Moehrbach" wrote in message
...
Post the function. HTH Otto
"Thomas M." wrote in message
...
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." wrote in message
...
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

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 InsertModule

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

On Wed, 14 Oct 2009 15:01:23 -0600, "Thomas M."
wrote:

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

"Thomas M." wrote in message
...
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

"Otto Moehrbach" wrote in message
...
Post the function. HTH Otto
"Thomas M." wrote in message
...
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." wrote in message
...
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