Here is what I am attempting to do:

If I have a column of numbers (rows 1 through 50)
rows 3,7,12 are colored yellow.
I want to add up the numbers in only columns 3, 7, and 12 (the cells that
are colored yellow)

Can this be done with a formula?

Thanks
George M

George

If the cells are colored by Conditional Formatting, use that criteria(on) to
SUM the cells.

If not, see Chip Pearson's site for Functions that operate on colored cells.

Gord Dibben Excel MVP

On Wed, 01 Sep 2004 00:02:19 GMT, "George M" <(E-Mail Removed)> wrote:

>Here is what I am attempting to do:
>
>If I have a column of numbers (rows 1 through 50)
>rows 3,7,12 are colored yellow.
>I want to add up the numbers in only columns 3, 7, and 12 (the cells that
>are colored yellow)
>
>Can this be done with a formula?
>
>Thanks
>George M
>

Gord

Thanks for the feedback. I am using fill colors - not conditional. I went to
Chip Pearson's site and studied the series on working with colors. I copied
his scripts and formula, but get nothing but #name? errors.
Don't know what is wrong unless I am using the wrong value for Yellow. I am
trying to search to see if the value is different that what I am using. I am
using 6 because I did read somewhere that Yellow was 6.

I also tried to use Chip's script to get the color of a cell, but get the
same error on that.

By the way, I am using Excel 2003, if that makes a difference.

George M



"Gord Dibben" <gorddibbATshawDOTca> wrote in message
news:(E-Mail Removed)...
> George
>
> If the cells are colored by Conditional Formatting, use that criteria(on)
to
> SUM the cells.
>
> If not, see Chip Pearson's site for Functions that operate on colored
cells.
>
> Gord Dibben Excel MVP
>
> On Wed, 01 Sep 2004 00:02:19 GMT, "George M" <(E-Mail Removed)>
wrote:
>
> >Here is what I am attempting to do:
> >
> >If I have a column of numbers (rows 1 through 50)
> >rows 3,7,12 are colored yellow.
> >I want to add up the numbers in only columns 3, 7, and 12 (the cells that
> >are colored yellow)
> >
> >Can this be done with a formula?
> >
> >Thanks
> >George M
> >
>

George

The #NAME error usually means that Excel does not recognise the Function.

I assume you copied the User Defined Functions from Chip's site.

You would paste them into a General Module in your workbook and Excel would
recognise them.

In fact, they would show up in the Function Wizard category "User Defined".

If not familiar with VBA and macros, see David McRitchie's site for more on
"getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

In the meantime..........

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + R to open Project Explorer.

Find your workbook/project and select it.

Right-click and Insert>Module. Paste Chip's code in there. Save the workbook
and hit ALT + Q to return to your workbook.

Enter the appropriate formula......probably =sumbycolor(range,6)

To get the index number of a cell........

Enter =cellcolorindex(cellref)

Gord

On Wed, 01 Sep 2004 14:00:49 GMT, "George M" <(E-Mail Removed)> wrote:

>Gord
>
>Thanks for the feedback. I am using fill colors - not conditional. I went to
>Chip Pearson's site and studied the series on working with colors. I copied
>his scripts and formula, but get nothing but #name? errors.
>Don't know what is wrong unless I am using the wrong value for Yellow. I am
>trying to search to see if the value is different that what I am using. I am
>using 6 because I did read somewhere that Yellow was 6.
>
>I also tried to use Chip's script to get the color of a cell, but get the
>same error on that.
>
>By the way, I am using Excel 2003, if that makes a difference.
>
>George M
>
>
>
>"Gord Dibben" <gorddibbATshawDOTca> wrote in message
>news:(E-Mail Removed)...
>> George
>>
>> If the cells are colored by Conditional Formatting, use that criteria(on)
>to
>> SUM the cells.
>>
>> If not, see Chip Pearson's site for Functions that operate on colored
>cells.
>>
>> Gord Dibben Excel MVP
>>
>> On Wed, 01 Sep 2004 00:02:19 GMT, "George M" <(E-Mail Removed)>
>wrote:
>>
>> >Here is what I am attempting to do:
>> >
>> >If I have a column of numbers (rows 1 through 50)
>> >rows 3,7,12 are colored yellow.
>> >I want to add up the numbers in only columns 3, 7, and 12 (the cells that
>> >are colored yellow)
>> >
>> >Can this be done with a formula?
>> >
>> >Thanks
>> >George M
>> >
>>
>

George,

It's more straightforward to put something in a column that identifies the
cells to be colored, then make cells colored as a function of that data
using either conditional formatting or a macro if you need more than three
colors. Then do your summing based on that column, not the color directly.
For more on this, see www.smokeylake.com/excel. Go to "Excel truths," and
read "Color as data."

--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"George M" <(E-Mail Removed)> wrote in message
news:ek8Zc.494814$(E-Mail Removed)...
> Here is what I am attempting to do:
>
> If I have a column of numbers (rows 1 through 50)
> rows 3,7,12 are colored yellow.
> I want to add up the numbers in only columns 3, 7, and 12 (the cells that
> are colored yellow)
>
> Can this be done with a formula?
>
> Thanks
> George M
>
>

Thanks again Gord,

That cleared it up. Works great.
What a great resource Chip's site is!

George M


"Gord Dibben" <gorddibbATshawDOTca> wrote in message
news:(E-Mail Removed)...
> George
>
> The #NAME error usually means that Excel does not recognise the Function.
>
> I assume you copied the User Defined Functions from Chip's site.
>
> You would paste them into a General Module in your workbook and Excel
would
> recognise them.
>
> In fact, they would show up in the Function Wizard category "User
Defined".
>
> If not familiar with VBA and macros, see David McRitchie's site for more
on
> "getting started".
>
> http://www.mvps.org/dmcritchie/excel/getstarted.htm
>
> In the meantime..........
>
> To create a General Module, hit ALT + F11 to open the Visual Basic Editor.
>
> Hit CRTL + R to open Project Explorer.
>
> Find your workbook/project and select it.
>
> Right-click and Insert>Module. Paste Chip's code in there. Save the
workbook
> and hit ALT + Q to return to your workbook.
>
> Enter the appropriate formula......probably =sumbycolor(range,6)
>
> To get the index number of a cell........
>
> Enter =cellcolorindex(cellref)
>
> Gord
>
> On Wed, 01 Sep 2004 14:00:49 GMT, "George M" <(E-Mail Removed)>
wrote:
>
> >Gord
> >
> >Thanks for the feedback. I am using fill colors - not conditional. I went
to
> >Chip Pearson's site and studied the series on working with colors. I
copied
> >his scripts and formula, but get nothing but #name? errors.
> >Don't know what is wrong unless I am using the wrong value for Yellow. I
am
> >trying to search to see if the value is different that what I am using. I
am
> >using 6 because I did read somewhere that Yellow was 6.
> >
> >I also tried to use Chip's script to get the color of a cell, but get the
> >same error on that.
> >
> >By the way, I am using Excel 2003, if that makes a difference.
> >
> >George M
> >
> >
> >
> >"Gord Dibben" <gorddibbATshawDOTca> wrote in message
> >news:(E-Mail Removed)...
> >> George
> >>
> >> If the cells are colored by Conditional Formatting, use that
criteria(on)
> >to
> >> SUM the cells.
> >>
> >> If not, see Chip Pearson's site for Functions that operate on colored
> >cells.
> >>
> >> Gord Dibben Excel MVP
> >>
> >> On Wed, 01 Sep 2004 00:02:19 GMT, "George M" <(E-Mail Removed)>
> >wrote:
> >>
> >> >Here is what I am attempting to do:
> >> >
> >> >If I have a column of numbers (rows 1 through 50)
> >> >rows 3,7,12 are colored yellow.
> >> >I want to add up the numbers in only columns 3, 7, and 12 (the cells
that
> >> >are colored yellow)
> >> >
> >> >Can this be done with a formula?
> >> >
> >> >Thanks
> >> >George M
> >> >
> >>
> >
>