View Single Post
  #6   Report Post  
Old December 31st 06, 02:55 PM posted to microsoft.public.excel.worksheet.functions
Dave Peterson Dave Peterson is offline
external usenet poster
First recorded activity by ExcelBanter: Jul 2006
Posts: 35,218
Default Color Cells Summing

Just a comment about UDF's not working across workbooks.

If your UDF is in a workbook named Book1.xls, you can refer to a function in
that workbook's project:


If your UDF is in an addin, then you can refer to it just like it was built into

Max wrote:

For those interested ..

Here's the revised sample, re-rendered incorporating Bob's suggestions <g:
Count n Sum Cells By FillColor_BobPhillips_ColorIndex_UDF.xls

Note that UDFs need to be implemented in the very *same* book that the
function is intended to be used. UDFs cannot work across books.

Bob: Trust the revised sample is ok with you. Let me know.

Here's wishing a wonderful 2007 to all !!
"Bob Phillips" wrote in message
Indeed it is beautifully rendered Max. If you intend to use this again,
I suggest that you also show how you can dynamically pick up the test
colour, that is by using

as I think this is a useful but not obvious feature (I know it could be
derived from what you do, but ...).

And also summing the values in the coloured range
=SUMPRODUCT(--(ColorIndex($B$2:$B$11)=ColorIndex(E2)),$B$2:$B$11 )


Post back if Max's workbook doesn't solve the problem for you. I have
that most problems are caused by people thinking that the function is a
built-in, not one that you have to copy and input to the workbook. Of
course, that may not be your problem, but give us as much detail as you
if and when posting back.


Bob Phillips


Dave Peterson