Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old December 30th 06, 06:07 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Dec 2006
Posts: 1
Default 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   Report Post  
Old December 30th 06, 06:38 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2006
Posts: 2,344
Default 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   Report Post  
Old December 30th 06, 10:28 PM 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

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" <[email protected] wrote in message news:[email protected]
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   Report Post  
Old December 31st 06, 12:45 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 10,593
Default 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" <[email protected] wrote in message news:[email protected]
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   Report Post  
Old December 31st 06, 02:35 PM 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

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   Report Post  
Old December 31st 06, 02:55 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 35,218
Default 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   Report Post  
Old December 31st 06, 03:51 PM 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

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   Report Post  
Old December 31st 06, 07:21 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2006
Posts: 2,344
Default 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   Report Post  
Old December 31st 06, 11:42 PM 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

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   Report Post  
Old December 31st 06, 11:56 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 22,907
Default 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




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