Home |
Search |
Today's Posts |
#1
![]() |
|||
|
|||
![]()
Hi there,
i have been trying to get the Color Index summing formula to work but i am unsuccessful, i went onto this site http://www.xldynamic.com/source/xld....r.html#summing but have gotten no where for some reason the formula will not work and i am left scratching my head again. If anyone can help i would really appreciate it as i have come to a complete standstill with my roster. Thanks Hanyana |
#2
![]() |
|||
|
|||
![]()
Hard to say what's wrong since you haven't provided much detail. First what
exactly are you trying to do (ignoring the websit)? -- Thanks, Shane Devenshire "HANYANA" wrote: Hi there, i have been trying to get the Color Index summing formula to work but i am unsuccessful, i went onto this site http://www.xldynamic.com/source/xld....r.html#summing but have gotten no where for some reason the formula will not work and i am left scratching my head again. If anyone can help i would really appreciate it as i have come to a complete standstill with my roster. Thanks Hanyana |
#3
![]() |
|||
|
|||
![]()
Try this working sample from my archives**:
http://www.savefile.com/files/377039 CountCellsByFillColor_Using_BobPhillips_ColorIndex _UDF.xls (Full details & beautifully rendered ..) **link in sign-off below -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "HANYANA" <[email protected] wrote in message news:[email protected] Hi there, i have been trying to get the Color Index summing formula to work but i am unsuccessful, i went onto this site http://www.xldynamic.com/source/xld....r.html#summing but have gotten no where for some reason the formula will not work and i am left scratching my head again. If anyone can help i would really appreciate it as i have come to a complete standstill with my roster. Thanks Hanyana |
#4
![]() |
|||
|
|||
![]()
Indeed it is beautifully rendered Max. If you intend to use this again, may
I suggest that you also show how you can dynamically pick up the test colour, that is by using =SUMPRODUCT(--(ColorIndex($B$2:$B$11)=ColorIndex(E2))) 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 ) Hanyana, Post back if Max's workbook doesn't solve the problem for you. I have found 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 can if and when posting back. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Max" wrote in message ... Try this working sample from my archives**: http://www.savefile.com/files/377039 CountCellsByFillColor_Using_BobPhillips_ColorIndex _UDF.xls (Full details & beautifully rendered ..) **link in sign-off below -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "HANYANA" <[email protected] wrote in message news:[email protected] Hi there, i have been trying to get the Color Index summing formula to work but i am unsuccessful, i went onto this site http://www.xldynamic.com/source/xld....r.html#summing but have gotten no where for some reason the formula will not work and i am left scratching my head again. If anyone can help i would really appreciate it as i have come to a complete standstill with my roster. Thanks Hanyana |
#5
![]() |
|||
|
|||
![]()
For those interested ..
Here's the revised sample, re-rendered incorporating Bob's suggestions <g: http://www.savefile.com/files/378485 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 !! -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Bob Phillips" wrote in message ... Indeed it is beautifully rendered Max. If you intend to use this again, may I suggest that you also show how you can dynamically pick up the test colour, that is by using =SUMPRODUCT(--(ColorIndex($B$2:$B$11)=ColorIndex(E2))) 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 ) Hanyana, Post back if Max's workbook doesn't solve the problem for you. I have found 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 can if and when posting back. -- HTH Bob Phillips |
#6
![]() |
|||
|
|||
![]()
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: =book1.xls!ColorIndex(...) If your UDF is in an addin, then you can refer to it just like it was built into excel. Max wrote: For those interested .. Here's the revised sample, re-rendered incorporating Bob's suggestions <g: http://www.savefile.com/files/378485 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 !! -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Bob Phillips" wrote in message ... Indeed it is beautifully rendered Max. If you intend to use this again, may I suggest that you also show how you can dynamically pick up the test colour, that is by using =SUMPRODUCT(--(ColorIndex($B$2:$B$11)=ColorIndex(E2))) 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 ) Hanyana, Post back if Max's workbook doesn't solve the problem for you. I have found 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 can if and when posting back. -- HTH Bob Phillips -- Dave Peterson |
#7
![]() |
|||
|
|||
![]()
Dave, thanks for the clarification.
-- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Dave Peterson" wrote in message ... 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: =book1.xls!ColorIndex(...) If your UDF is in an addin, then you can refer to it just like it was built into excel. |
#8
![]() |
|||
|
|||
![]()
Speaking of 2007... In Excel 2007 you can count by color, sum by color,
filter by color, sort by color and more. -- Looking ahead, Shane Devenshire "Max" wrote: Dave, thanks for the clarification. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Dave Peterson" wrote in message ... 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: =book1.xls!ColorIndex(...) If your UDF is in an addin, then you can refer to it just like it was built into excel. |
#9
![]() |
|||
|
|||
![]()
Thanks, Shane. I just moved from xl97 to xl2003 in May 06. Thought that was
a quantum leap. Maybe I'll get to use xl2007 in 2015 thereabouts, going by the same rate? <bg -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "ShaneDevenshire" wrote in message ... Speaking of 2007... In Excel 2007 you can count by color, sum by color, filter by color, sort by color and more. -- Looking ahead, Shane Devenshire |
#10
![]() |
|||
|
|||
![]()
You and me both Max<g
Happy New Year Gord On Mon, 1 Jan 2007 07:42:09 +0800, "Max" wrote: Thanks, Shane. I just moved from xl97 to xl2003 in May 06. Thought that was a quantum leap. Maybe I'll get to use xl2007 in 2015 thereabouts, going by the same rate? <bg |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Summing (or counting) cells until there is a blank cell | Excel Discussion (Misc queries) | |||
Total Not Summing Correctly on Calculated Fields in Pivot Table | Excel Discussion (Misc queries) | |||
Summing large numbers with decimals in Excel | Excel Discussion (Misc queries) | |||
summing values appearing in col B when col A has been filtered | Excel Worksheet Functions | |||
Cumulative Summing | Excel Discussion (Misc queries) |