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.

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

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

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

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

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

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

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