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

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

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

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

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

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

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.

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.

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

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

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

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

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

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.

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

.. 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

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

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

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

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

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

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

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

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

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!

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
---

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
---

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
---

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

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