ColorIndex

S

skat

Where can I find the ColorIndex codes?
--
skat

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/com...dg=microsoft.public.excel.worksheet.functions
 
S

skat

I am trying to use this statement in a cell:
=SUMPRODUCT(--(ColorIndex(A1:A3)=3),A1:A3), the cell returns #Name. The
only cells in use are A1 containing 24, A2 containing 16 and A3 containing
the above formula. Cell A1 background is colored red. What is wrong? I
would expect the sum to be 24.
 
S

skat

I'm using Excel 2007. It already contains a function called SumProduct but
it doesn't accept (--(colorindex(A1:A2)=3),A1:A3). Could you please give me
the code that I have to enter in a macro for colorindex? How can I use the
Excel 2007 SumProduct function to selectively sum only the cells with a red
background?
skat
 
P

Peo Sjoblom

In 2007 you can actually apply autofilter using color, then use subtotal to
sum

=SUBTOTAL(9,A2:A50)



http://www.cpearson.com/excel/colors.htm

look for "Summing the values of cells with a specific color"
copy from Function SumByColor until and including End Function

however since 2007 has that built in I would suggest the filter way

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey
 
Top