Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #11   Report Post  
Old January 1st 07, 12:07 AM posted to microsoft.public.excel.worksheet.functions
Max Max is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 9,221
Default 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   Report Post  
Old January 1st 07, 12:18 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 2,886
Default 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   Report Post  
Old January 1st 07, 12:38 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2007
Posts: 7
Default 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   Report Post  
Old January 1st 07, 12:44 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 22,907
Default 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   Report Post  
Old January 1st 07, 12:53 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2007
Posts: 7
Default 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   Report Post  
Old January 1st 07, 01:05 AM posted to microsoft.public.excel.worksheet.functions
Max Max is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 9,221
Default 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" <[email protected] wrote in message
news:[email protected]
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   Report Post  
Old January 1st 07, 01:06 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2006
Posts: 2,344
Default 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   Report Post  
Old January 1st 07, 01:07 AM posted to microsoft.public.excel.worksheet.functions
Max Max is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 9,221
Default 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   Report Post  
Old January 1st 07, 01:24 AM posted to microsoft.public.excel.worksheet.functions
Max Max is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 9,221
Default Color Cells Summing

Good to hear you got it working !
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"HANYANA via OfficeKB.com" <[email protected] wrote in message
news:[email protected]
Hey Bob,
Scratch that, I've played with it and it's working.
You guys are great. THANK YOU so much!

Han



  #20   Report Post  
Old January 1st 07, 05:55 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2007
Posts: 7
Default 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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Summing (or counting) cells until there is a blank cell Box815 Excel Discussion (Misc queries) 3 November 17th 06 03:35 PM
Total Not Summing Correctly on Calculated Fields in Pivot Table Ronster Excel Discussion (Misc queries) 0 October 19th 06 10:48 PM
Summing large numbers with decimals in Excel PradeepKel Excel Discussion (Misc queries) 1 June 6th 06 04:12 AM
summing values appearing in col B when col A has been filtered choc_penguin Excel Worksheet Functions 3 February 3rd 06 11:18 AM
Cumulative Summing Carpie Excel Discussion (Misc queries) 3 February 11th 05 04:35 PM


All times are GMT +1. The time now is 04:42 PM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017