Color Cells Summing

[HANYANA]

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

 

[Guest]

Hard to say what's wrong since you haven't provided much detail. First what
exactly are you trying to do (ignoring the websit)?

 

[Max]

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

 

[Bob Phillips]

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]

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

 

[Dave Peterson]

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]

Dave, thanks for the clarification.

 

[Guest]

Speaking of 2007... In Excel 2007 you can count by color, sum by color,
filter by color, sort by color and more.

 

[Max]

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>

 

[Gord Dibben]

You and me both Max<g>

Happy New Year


Gord

 

[Max]

Aha, great to have company here <g>
Happy New Year, Gord !

 

[Roger Govier]

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.

 

[HANYANA via OfficeKB.com]

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

 

[Gord Dibben]

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

 

[HANYANA via OfficeKB.com]

Hey Bob,
Scratch that, I've played with it and it's working.
You guys are great. THANK YOU so much!

Han

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]

 

[Max]

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

 

[Guest]

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.

 

[Max]

I made the upgrade to Office 2007 ...
... lucky you ! <g>

Happy New Year, Roger!

 

[Max]

Good to hear you got it working !

 

[HANYANA via OfficeKB.com]

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

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