Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Color Cells Summing
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Color Cells Summing
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Color Cells Summing
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" <u30570@uwe wrote in message news:6b86575e611de@uwe... 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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Color Cells Summing
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" <u30570@uwe wrote in message news:6b86575e611de@uwe... 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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Color Cells Summing
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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: =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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Color Cells Summing
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Color Cells Summing
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Color Cells Summing
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Color Cells Summing
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 |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Color Cells Summing
Aha, great to have company here <g
Happy New Year, Gord ! -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Gord Dibben" <gorddibbATshawDOTca wrote in message ... You and me both Max<g Happy New Year Gord |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Color Cells Summing
Hi Max and Gord
I made the upgrade to Office 2007 over Christmas and I think XL2007 is great. Happy New Year to you both - we've just seen in the new year here in the UK so off to bed now. -- Regards Roger Govier "Max" wrote in message ... Aha, great to have company here <g Happy New Year, Gord ! -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Gord Dibben" <gorddibbATshawDOTca wrote in message ... You and me both Max<g Happy New Year Gord |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Color Cells Summing
Hi Bob,
i tried using Max's work book and the formula sits well but it comes up with the #NAME? thing and doesn't actually count or add up the number or red cells. I guess i'm a real dumb blonde.(i can say that as i am blonde) :) My scenario I have a roster i made up for the whole year, it has colored cells for annual leave and training days because i have the staff work areas set up with formulas to make sure i have the correct amount of staff in a set area. The staff have area codes that sometimes need to be changed which alters my staff count. Therefore i need to be able to sum all the red cells (annual leave) and all the blue cells (training days) to a different part of the roster so that i can keep track on whats happening with who and the amount of days each employee has has in a.l and training to keep up with company protocol. I know it sounds a mess the original sheet looks great and works well but i just cant get the last bit to work. If you can help i would really appreciate it. I just have no clue anymore and am completely dazed and confuzed. Hope you have a great 2007 Han Bob Phillips wrote: 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. Try this working sample from my archives**: http://www.savefile.com/files/377039 [quoted text clipped - 12 lines] Thanks Hanyana -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200701/1 |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Color Cells Summing
Thanks for the encouragement Roger.
Happy New Year.....4:41pm here at GMT - 8 on the west coast of Canada. I won't make it 'til midnight......maybe set the alarm and arise for a chorus of Auld Lang Syne Gord On Mon, 1 Jan 2007 00:18:40 -0000, "Roger Govier" wrote: Hi Max and Gord I made the upgrade to Office 2007 over Christmas and I think XL2007 is great. Happy New Year to you both - we've just seen in the new year here in the UK so off to bed now. |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Color Cells Summing
Hey Bob,
Scratch that, I've played with it and it's working. You guys are great. THANK YOU so much! Han HANYANA wrote: Hi Bob, i tried using Max's work book and the formula sits well but it comes up with the #NAME? thing and doesn't actually count or add up the number or red cells. I guess i'm a real dumb blonde.(i can say that as i am blonde) :) My scenario I have a roster i made up for the whole year, it has colored cells for annual leave and training days because i have the staff work areas set up with formulas to make sure i have the correct amount of staff in a set area. The staff have area codes that sometimes need to be changed which alters my staff count. Therefore i need to be able to sum all the red cells (annual leave) and all the blue cells (training days) to a different part of the roster so that i can keep track on whats happening with who and the amount of days each employee has has in a.l and training to keep up with company protocol. I know it sounds a mess the original sheet looks great and works well but i just cant get the last bit to work. If you can help i would really appreciate it. I just have no clue anymore and am completely dazed and confuzed. Hope you have a great 2007 Han 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 [quoted text clipped - 22 lines] Thanks Hanyana -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200701/1 |
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Color Cells Summing
.. it comes up with the #NAME? thing ..
Try implementing Bob's UDF into your actual book, before using similar formulas therein. This should help get it going. Hang around for other insights from Bob and others .. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "HANYANA via OfficeKB.com" <u30570@uwe wrote in message news:6b9b75c24fe53@uwe... Hi Bob, i tried using Max's work book and the formula sits well but it comes up with the #NAME? thing and doesn't actually count or add up the number or red cells. I guess i'm a real dumb blonde.(i can say that as i am blonde) :) My scenario I have a roster i made up for the whole year, it has colored cells for annual leave and training days because i have the staff work areas set up with formulas to make sure i have the correct amount of staff in a set area. The staff have area codes that sometimes need to be changed which alters my staff count. Therefore i need to be able to sum all the red cells (annual leave) and all the blue cells (training days) to a different part of the roster so that i can keep track on whats happening with who and the amount of days each employee has has in a.l and training to keep up with company protocol. I know it sounds a mess the original sheet looks great and works well but i just cant get the last bit to work. If you can help i would really appreciate it. I just have no clue anymore and am completely dazed and confuzed. Hope you have a great 2007 Han |
#17
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Color Cells Summing
Since you're still having a problem, here is another alternative:
Function CountFormats(R As Range, E As Range) As Integer Dim cell As Range Dim Total As Integer Dim T As Boolean Set S = E.Cells(1, 1) Total = 0 For Each cell In R T = True With cell If .Font.ColorIndex < S.Font.ColorIndex Then T = False If .Interior.ColorIndex < S.Interior.ColorIndex Then T = False If .Font.Bold < S.Font.Bold Then T = False If .Font.Italic < S.Font.Italic Then T = False If .Font.Underline < S.Font.Underline Then T = False End With If T = True Then Total = Total + 1 End If Next cell CountFormats = Total End Function In the spreadsheet you enter the formula =CountFormats(D1:I24,L12) where D1:I24 is the range you want to count the format for and cell L12 is a cell formatted with that format. This function check interior color (fill), font color, bold, italic and underline. If you don't want any of those checked just remove the appropriate single line from: If .Font.ColorIndex < S.Font.ColorIndex Then T = False If .Interior.ColorIndex < S.Interior.ColorIndex Then T = False If .Font.Bold < S.Font.Bold Then T = False If .Font.Italic < S.Font.Italic Then T = False If .Font.Underline < S.Font.Underline Then T = False You will need to add the code to a module in the file you are working with, or the Personal Macro Workbook, or an XLA addin. -- Cheers, 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 |
#18
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Color Cells Summing
I made the upgrade to Office 2007 ...
... lucky you ! <g Happy New Year, Roger! -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Roger Govier" wrote in message ... Hi Max and Gord I made the upgrade to Office 2007 over Christmas and I think XL2007 is great. Happy New Year to you both - we've just seen in the new year here in the UK so off to bed now. -- Regards Roger Govier |
#19
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Color Cells Summing
Good to hear you got it working !
-- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "HANYANA via OfficeKB.com" <u30570@uwe wrote in message news:6b9b970e20fd9@uwe... Hey Bob, Scratch that, I've played with it and it's working. You guys are great. THANK YOU so much! Han |
#20
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Color Cells Summing
Hey Max,
I just went back into it and it has come up with the #NAME? thing again but i have used urs and Bobs module i don't know how to get it to stay so the formulas aill still work... They we working before i logged off to go to lunch :( Sorry Han Max wrote: .. it comes up with the #NAME? thing .. Try implementing Bob's UDF into your actual book, before using similar formulas therein. This should help get it going. Hang around for other insights from Bob and others .. Hi Bob, [quoted text clipped - 28 lines] have a great 2007 Han -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200701/1 |
#21
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Color Cells Summing
Hey Max & Bob,
i Figured it out in the end, the securoty was up too high ... derr.. Sorry to be a pain but can you guys help me with one last thing? i need to share some data from cells on this worksheet to another layed out work sheet and i dont know how. i know i am a pain eg : SIMON 0 16 ADRIAN 0 0 RAY 0 0 DANE 0 0 These numbers all have formulas which change when i add the colours. ( that's why i thought you could help as you did a fantastic job helping me there). Well, Happy New Year, i hope you don't mind helping me. Thanks Han -- Message posted via http://www.officekb.com |
#22
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Color Cells Summing
Good to hear you resolved what was bugging you.
Suggest you put in a fresh posting for your new query -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- HANYANA via OfficeKB.com wrote: Hey Max & Bob, i Figured it out in the end, the securoty was up too high ... derr.. Sorry to be a pain but can you guys help me with one last thing? i need to share some data from cells on this worksheet to another layed out work sheet and i dont know how. i know i am a pain eg : SIMON 0 16 ADRIAN 0 0 RAY 0 0 DANE 0 0 These numbers all have formulas which change when i add the colours. ( that's why i thought you could help as you did a fantastic job helping me there). Well, Happy New Year, i hope you don't mind helping me. Thanks Han -- Message posted via http://www.officekb.com |
#23
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Color Cells Summing
I've been using 2007 for a year or so now in various Beta TRM incarnations,
and I think I might just get USEDS to it by 2015! "Max" wrote in message ... Aha, great to have company here <g Happy New Year, Gord ! -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Gord Dibben" <gorddibbATshawDOTca wrote in message ... You and me both Max<g Happy New Year Gord |
#24
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Color Cells Summing
Han,
I am not really getting the problem. Can you explain from the bottom? -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "HANYANA via OfficeKB.com" <u30570@uwe wrote in message news:6ba6c1ecf4617@uwe... Hey Max & Bob, i Figured it out in the end, the securoty was up too high ... derr.. Sorry to be a pain but can you guys help me with one last thing? i need to share some data from cells on this worksheet to another layed out work sheet and i dont know how. i know i am a pain eg : SIMON 0 16 ADRIAN 0 0 RAY 0 0 DANE 0 0 These numbers all have formulas which change when i add the colours. ( that's why i thought you could help as you did a fantastic job helping me there). Well, Happy New Year, i hope you don't mind helping me. Thanks Han -- Message posted via http://www.officekb.com |
#25
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Color Cells Summing
Bob,
Happy New Year ! Ahh, I'll just roll along and let each year take care of itself <g -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Bob Phillips" wrote in message ... I've been using 2007 for a year or so now in various Beta TRM incarnations, and I think I might just get USEDS to it by 2015! |
#26
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Color Cells Summing
Bob,
... 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 Bob: Trust the revised sample is ok with you. Let me know. In the absence of comments from you to the contrary, re the above in earlier response, I'll take it you're happy with it? -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#27
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Color Cells Summing
ok, no choice then ..
I'll take it you're fine with it, Bob <g -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Max" wrote in message ... Bob, ... 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 Bob: Trust the revised sample is ok with you. Let me know. In the absence of comments from you to the contrary, re the above in earlier response, I'll take it you're happy with it? -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#28
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Color Cells Summing
Sorry, of course I am Max. It is your file, I just made a suggestion to make
it (hopefully) better. Bob "Max" wrote in message ... ok, no choice then .. I'll take it you're fine with it, Bob <g -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Max" wrote in message ... Bob, ... 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 Bob: Trust the revised sample is ok with you. Let me know. In the absence of comments from you to the contrary, re the above in earlier response, I'll take it you're happy with it? -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#29
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Color Cells Summing
Glad to hear that, Bob !
Cheers -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Bob Phillips" wrote in message ... Sorry, of course I am Max. It is your file, I just made a suggestion to make it (hopefully) better. Bob |
#30
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Color Cells Summing
Glad to hear that, Bob !
Cheers -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- Bob Phillips wrote: Sorry, of course I am Max. It is your file, I just made a suggestion to make it (hopefully) better. Bob |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
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) |