View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Thomas M. Thomas M. is offline
external usenet poster
 
Posts: 68
Default Conditional Sum Based on Cell Background Color

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