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