PC Review


Reply
Thread Tools Rate Thread

SUMIF cell is colored

 
 
=?Utf-8?B?ZHRlbmN6YQ==?=
Guest
Posts: n/a
 
      17th Aug 2005
I have cells in a column, some colored yellow, some not. I am trying to use
the SUMIF function to sum only the cells in the range that are colored. Can
this be done? Can I enter something in the "criteria" part of the formula
that can do this?

=SUMIF(D313,"criteria",D313)

I also tried to use the CELL function's color feature, but I couldn't get it
to work right. I don't know how to get Excel to recognize if a cell is
colored in a formula.
=CELL("color",cell)

It might just be that I don't know what this means in Help:

"color" --> 1 if the cell is formatted in color for negative values;
otherwise returns 0 (zero).

Can anyone help?
 
Reply With Quote
 
 
 
 
=?Utf-8?B?RHVrZSBDYXJleQ==?=
Guest
Posts: n/a
 
      17th Aug 2005
See Chip Pearson's site

http://www.cpearson.com/excel/SortByColor.htm


"dtencza" wrote:

> I have cells in a column, some colored yellow, some not. I am trying to use
> the SUMIF function to sum only the cells in the range that are colored. Can
> this be done? Can I enter something in the "criteria" part of the formula
> that can do this?
>
> =SUMIF(D313,"criteria",D313)
>
> I also tried to use the CELL function's color feature, but I couldn't get it
> to work right. I don't know how to get Excel to recognize if a cell is
> colored in a formula.
> =CELL("color",cell)
>
> It might just be that I don't know what this means in Help:
>
> "color" --> 1 if the cell is formatted in color for negative values;
> otherwise returns 0 (zero).
>
> Can anyone help?

 
Reply With Quote
 
 
 
 
=?Utf-8?B?VG9kZCBGLg==?=
Guest
Posts: n/a
 
      17th Aug 2005
ASAP-UTILITIES.com - some very handy tools on this add in including color
options

"dtencza" wrote:

> I have cells in a column, some colored yellow, some not. I am trying to use
> the SUMIF function to sum only the cells in the range that are colored. Can
> this be done? Can I enter something in the "criteria" part of the formula
> that can do this?
>
> =SUMIF(D313,"criteria",D313)
>
> I also tried to use the CELL function's color feature, but I couldn't get it
> to work right. I don't know how to get Excel to recognize if a cell is
> colored in a formula.
> =CELL("color",cell)
>
> It might just be that I don't know what this means in Help:
>
> "color" --> 1 if the cell is formatted in color for negative values;
> otherwise returns 0 (zero).
>
> Can anyone help?

 
Reply With Quote
 
=?Utf-8?B?ZHRlbmN6YQ==?=
Guest
Posts: n/a
 
      17th Aug 2005
Thanks, Duke. This looks like just what I need, especially this link:

http://www.cpearson.com/excel/CFColors.htm

How do I actually use these functions? I copied and pasted these functions
into the Visual Basic editor and hit Save. The functions still don't work,
though. I get the #NAME? error, which I think is because I didn't put them
in there the right way, so Excel doesn't recognize the function yet.

I am probably doing something wrong since I haven't tried to do something
like this before? Would you (or anyone else) know the right way to get these
functions?

Thank you for any help you can offer.


"Duke Carey" wrote:

> See Chip Pearson's site
>
> http://www.cpearson.com/excel/SortByColor.htm
>
>
> "dtencza" wrote:
>
> > I have cells in a column, some colored yellow, some not. I am trying to use
> > the SUMIF function to sum only the cells in the range that are colored. Can
> > this be done? Can I enter something in the "criteria" part of the formula
> > that can do this?
> >
> > =SUMIF(D313,"criteria",D313)
> >
> > I also tried to use the CELL function's color feature, but I couldn't get it
> > to work right. I don't know how to get Excel to recognize if a cell is
> > colored in a formula.
> > =CELL("color",cell)
> >
> > It might just be that I don't know what this means in Help:
> >
> > "color" --> 1 if the cell is formatted in color for negative values;
> > otherwise returns 0 (zero).
> >
> > Can anyone help?

 
Reply With Quote
 
David McRitchie
Guest
Posts: n/a
 
      17th Aug 2005
This thread is a parallel of another similar thread (that was actually in two threads), see

http://google.com/groups?threadm=Oyk...TNGP14.phx.gbl

then see
http://google.com/groups?threadm=%23...TNGP12.phx.gbl
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"dtencza" <(E-Mail Removed)> wrote in message news:(E-Mail Removed)...
> Thanks, Duke. This looks like just what I need, especially this link:
>
> http://www.cpearson.com/excel/CFColors.htm
>
> How do I actually use these functions? I copied and pasted these functions
> into the Visual Basic editor and hit Save. The functions still don't work,
> though. I get the #NAME? error, which I think is because I didn't put them
> in there the right way, so Excel doesn't recognize the function yet.
>
> I am probably doing something wrong since I haven't tried to do something
> like this before? Would you (or anyone else) know the right way to get these
> functions?
>
> Thank you for any help you can offer.
>
>
> "Duke Carey" wrote:
>
> > See Chip Pearson's site
> >
> > http://www.cpearson.com/excel/SortByColor.htm
> >
> >
> > "dtencza" wrote:
> >
> > > I have cells in a column, some colored yellow, some not. I am trying to use
> > > the SUMIF function to sum only the cells in the range that are colored. Can
> > > this be done? Can I enter something in the "criteria" part of the formula
> > > that can do this?
> > >
> > > =SUMIF(D313,"criteria",D313)
> > >
> > > I also tried to use the CELL function's color feature, but I couldn't get it
> > > to work right. I don't know how to get Excel to recognize if a cell is
> > > colored in a formula.
> > > =CELL("color",cell)
> > >
> > > It might just be that I don't know what this means in Help:
> > >
> > > "color" --> 1 if the cell is formatted in color for negative values;
> > > otherwise returns 0 (zero).
> > >
> > > Can anyone help?



 
Reply With Quote
 
=?Utf-8?B?ZHRlbmN6YQ==?=
Guest
Posts: n/a
 
      18th Aug 2005
Thanks! I have the functions working!!! I'm so close!

On the page I referred to before, the IndexColorOfCF, ColorOfCF, and
CountOfCF all work for me. The SumByCFColorIndex no longer returns an error,
but it is always evaluating to zero. I am putting the range, then an integer
as the function arguments.

I tried 0,1,2,-1, and others to try and see if it would sum the values of
the colored cells in the range I specified. I tried to look at the code, but
I don't know what integer it is looking for for it to work.

It is the last function on this page. If this would work, I'm all set! If
someone
who understands this code could tell me, that would be great.

Here is the page with the functions:
http://www.cpearson.com/excel/CFColors.htm


"David McRitchie" wrote:

> This thread is a parallel of another similar thread (that was actually in two threads), see
>
> http://google.com/groups?threadm=Oyk...TNGP14.phx.gbl
>
> then see
> http://google.com/groups?threadm=%23...TNGP12.phx.gbl
> ---
> HTH,
> David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
> My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
> Search Page: http://www.mvps.org/dmcritchie/excel/search.htm
>
> "dtencza" <(E-Mail Removed)> wrote in message news:(E-Mail Removed)...
> > Thanks, Duke. This looks like just what I need, especially this link:
> >
> > http://www.cpearson.com/excel/CFColors.htm
> >
> > How do I actually use these functions? I copied and pasted these functions
> > into the Visual Basic editor and hit Save. The functions still don't work,
> > though. I get the #NAME? error, which I think is because I didn't put them
> > in there the right way, so Excel doesn't recognize the function yet.
> >
> > I am probably doing something wrong since I haven't tried to do something
> > like this before? Would you (or anyone else) know the right way to get these
> > functions?
> >
> > Thank you for any help you can offer.
> >
> >
> > "Duke Carey" wrote:
> >
> > > See Chip Pearson's site
> > >
> > > http://www.cpearson.com/excel/SortByColor.htm
> > >
> > >
> > > "dtencza" wrote:
> > >
> > > > I have cells in a column, some colored yellow, some not. I am trying to use
> > > > the SUMIF function to sum only the cells in the range that are colored. Can
> > > > this be done? Can I enter something in the "criteria" part of the formula
> > > > that can do this?
> > > >
> > > > =SUMIF(D313,"criteria",D313)
> > > >
> > > > I also tried to use the CELL function's color feature, but I couldn't get it
> > > > to work right. I don't know how to get Excel to recognize if a cell is
> > > > colored in a formula.
> > > > =CELL("color",cell)
> > > >
> > > > It might just be that I don't know what this means in Help:
> > > >
> > > > "color" --> 1 if the cell is formatted in color for negative values;
> > > > otherwise returns 0 (zero).
> > > >
> > > > Can anyone help?

>
>
>

 
Reply With Quote
 
David McRitchie
Guest
Posts: n/a
 
      18th Aug 2005
sorry, I didn't realize you were doing the Conditional Color indexing.
The code is incorrect. I sent corrections to Chip and it would be mainly
the last part of the large UDF that have the biggest problem, and it is
invoked by the other UDF on the page. Since I ONLY use
formulas I found that they did not work for me if formatted to a row, but
okay if formatted to a column (I think it was). . Look for corrected user
defined functions (UDF) in
http://www.mvps.org/dmcritchie/code/xl2htmlx.txt

below a line of
'-- ///////////////////////////////////////////////

The function you indicate is not there, but it invokes a big UDF that
needs a correction.

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"dtencza" <(E-Mail Removed)> wrote in message news:(E-Mail Removed)...
> Thanks! I have the functions working!!! I'm so close!
>
> On the page I referred to before, the IndexColorOfCF, ColorOfCF, and
> CountOfCF all work for me. The SumByCFColorIndex no longer returns an error,
> but it is always evaluating to zero. I am putting the range, then an integer
> as the function arguments.
>
> I tried 0,1,2,-1, and others to try and see if it would sum the values of
> the colored cells in the range I specified. I tried to look at the code, but
> I don't know what integer it is looking for for it to work.
>
> It is the last function on this page. If this would work, I'm all set! If
> someone
> who understands this code could tell me, that would be great.
>
> Here is the page with the functions:
> http://www.cpearson.com/excel/CFColors.htm
>
>
> "David McRitchie" wrote:
>
> > This thread is a parallel of another similar thread (that was actually in two threads), see
> >
> > http://google.com/groups?threadm=Oyk...TNGP14.phx.gbl
> >
> > then see
> > http://google.com/groups?threadm=%23...TNGP12.phx.gbl
> > ---
> > HTH,
> > David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
> > My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
> > Search Page: http://www.mvps.org/dmcritchie/excel/search.htm
> >
> > "dtencza" <(E-Mail Removed)> wrote in message news:(E-Mail Removed)...
> > > Thanks, Duke. This looks like just what I need, especially this link:
> > >
> > > http://www.cpearson.com/excel/CFColors.htm
> > >
> > > How do I actually use these functions? I copied and pasted these functions
> > > into the Visual Basic editor and hit Save. The functions still don't work,
> > > though. I get the #NAME? error, which I think is because I didn't put them
> > > in there the right way, so Excel doesn't recognize the function yet.
> > >
> > > I am probably doing something wrong since I haven't tried to do something
> > > like this before? Would you (or anyone else) know the right way to get these
> > > functions?
> > >
> > > Thank you for any help you can offer.
> > >
> > >
> > > "Duke Carey" wrote:
> > >
> > > > See Chip Pearson's site
> > > >
> > > > http://www.cpearson.com/excel/SortByColor.htm
> > > >
> > > >
> > > > "dtencza" wrote:
> > > >
> > > > > I have cells in a column, some colored yellow, some not. I am trying to use
> > > > > the SUMIF function to sum only the cells in the range that are colored. Can
> > > > > this be done? Can I enter something in the "criteria" part of the formula
> > > > > that can do this?
> > > > >
> > > > > =SUMIF(D313,"criteria",D313)
> > > > >
> > > > > I also tried to use the CELL function's color feature, but I couldn't get it
> > > > > to work right. I don't know how to get Excel to recognize if a cell is
> > > > > colored in a formula.
> > > > > =CELL("color",cell)
> > > > >
> > > > > It might just be that I don't know what this means in Help:
> > > > >
> > > > > "color" --> 1 if the cell is formatted in color for negative values;
> > > > > otherwise returns 0 (zero).
> > > > >
> > > > > Can anyone help?

> >
> >
> >



 
Reply With Quote
 
=?Utf-8?B?ZHRlbmN6YQ==?=
Guest
Posts: n/a
 
      18th Aug 2005
I clicked this link and it says the page cannot be found. Is this really the
link?

"David McRitchie" wrote:

> sorry, I didn't realize you were doing the Conditional Color indexing.
> The code is incorrect. I sent corrections to Chip and it would be mainly
> the last part of the large UDF that have the biggest problem, and it is
> invoked by the other UDF on the page. Since I ONLY use
> formulas I found that they did not work for me if formatted to a row, but
> okay if formatted to a column (I think it was). . Look for corrected user
> defined functions (UDF) in
> http://www.mvps.org/dmcritchie/code/xl2htmlx.txt
>
> below a line of
> '-- ///////////////////////////////////////////////
>
> The function you indicate is not there, but it invokes a big UDF that
> needs a correction.
>
> ---
> HTH,
> David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
> My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
> Search Page: http://www.mvps.org/dmcritchie/excel/search.htm
>
> "dtencza" <(E-Mail Removed)> wrote in message news:(E-Mail Removed)...
> > Thanks! I have the functions working!!! I'm so close!
> >
> > On the page I referred to before, the IndexColorOfCF, ColorOfCF, and
> > CountOfCF all work for me. The SumByCFColorIndex no longer returns an error,
> > but it is always evaluating to zero. I am putting the range, then an integer
> > as the function arguments.
> >
> > I tried 0,1,2,-1, and others to try and see if it would sum the values of
> > the colored cells in the range I specified. I tried to look at the code, but
> > I don't know what integer it is looking for for it to work.
> >
> > It is the last function on this page. If this would work, I'm all set! If
> > someone
> > who understands this code could tell me, that would be great.
> >
> > Here is the page with the functions:
> > http://www.cpearson.com/excel/CFColors.htm
> >
> >
> > "David McRitchie" wrote:
> >
> > > This thread is a parallel of another similar thread (that was actually in two threads), see
> > >
> > > http://google.com/groups?threadm=Oyk...TNGP14.phx.gbl
> > >
> > > then see
> > > http://google.com/groups?threadm=%23...TNGP12.phx.gbl
> > > ---
> > > HTH,
> > > David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
> > > My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
> > > Search Page: http://www.mvps.org/dmcritchie/excel/search.htm
> > >
> > > "dtencza" <(E-Mail Removed)> wrote in message news:(E-Mail Removed)...
> > > > Thanks, Duke. This looks like just what I need, especially this link:
> > > >
> > > > http://www.cpearson.com/excel/CFColors.htm
> > > >
> > > > How do I actually use these functions? I copied and pasted these functions
> > > > into the Visual Basic editor and hit Save. The functions still don't work,
> > > > though. I get the #NAME? error, which I think is because I didn't put them
> > > > in there the right way, so Excel doesn't recognize the function yet.
> > > >
> > > > I am probably doing something wrong since I haven't tried to do something
> > > > like this before? Would you (or anyone else) know the right way to get these
> > > > functions?
> > > >
> > > > Thank you for any help you can offer.
> > > >
> > > >
> > > > "Duke Carey" wrote:
> > > >
> > > > > See Chip Pearson's site
> > > > >
> > > > > http://www.cpearson.com/excel/SortByColor.htm
> > > > >
> > > > >
> > > > > "dtencza" wrote:
> > > > >
> > > > > > I have cells in a column, some colored yellow, some not. I am trying to use
> > > > > > the SUMIF function to sum only the cells in the range that are colored. Can
> > > > > > this be done? Can I enter something in the "criteria" part of the formula
> > > > > > that can do this?
> > > > > >
> > > > > > =SUMIF(D313,"criteria",D313)
> > > > > >
> > > > > > I also tried to use the CELL function's color feature, but I couldn't get it
> > > > > > to work right. I don't know how to get Excel to recognize if a cell is
> > > > > > colored in a formula.
> > > > > > =CELL("color",cell)
> > > > > >
> > > > > > It might just be that I don't know what this means in Help:
> > > > > >
> > > > > > "color" --> 1 if the cell is formatted in color for negative values;
> > > > > > otherwise returns 0 (zero).
> > > > > >
> > > > > > Can anyone help?
> > >
> > >
> > >

>
>
>

 
Reply With Quote
 
David McRitchie
Guest
Posts: n/a
 
      18th Aug 2005
Sorry about that, left out the excel directory, the correct link is
http://www.mvps.org/dmcritchie/excel/code/xl2htmlx.txt


"dtencza" <(E-Mail Removed)> wrote in message news:(E-Mail Removed)...
> I clicked this link and it says the page cannot be found. Is this really the
> link?
>
> "David McRitchie" wrote:
>
> > sorry, I didn't realize you were doing the Conditional Color indexing.
> > The code is incorrect. I sent corrections to Chip and it would be mainly
> > the last part of the large UDF that have the biggest problem, and it is
> > invoked by the other UDF on the page. Since I ONLY use
> > formulas I found that they did not work for me if formatted to a row, but
> > okay if formatted to a column (I think it was). . Look for corrected user
> > defined functions (UDF) in
> > http://www.mvps.org/dmcritchie/excel/code/xl2htmlx.txt <--- corrected link >
> > below a line of
> > '-- ///////////////////////////////////////////////
> >
> > The function you indicate is not there, but it invokes a big UDF that
> > needs a correction.
> >
> > ---
> > HTH,
> > David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
> > My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
> > Search Page: http://www.mvps.org/dmcritchie/excel/search.htm
> >
> > "dtencza" <(E-Mail Removed)> wrote in message news:(E-Mail Removed)...
> > > Thanks! I have the functions working!!! I'm so close!
> > >
> > > On the page I referred to before, the IndexColorOfCF, ColorOfCF, and
> > > CountOfCF all work for me. The SumByCFColorIndex no longer returns an error,
> > > but it is always evaluating to zero. I am putting the range, then an integer
> > > as the function arguments.
> > >
> > > I tried 0,1,2,-1, and others to try and see if it would sum the values of
> > > the colored cells in the range I specified. I tried to look at the code, but
> > > I don't know what integer it is looking for for it to work.
> > >
> > > It is the last function on this page. If this would work, I'm all set! If
> > > someone
> > > who understands this code could tell me, that would be great.
> > >
> > > Here is the page with the functions:
> > > http://www.cpearson.com/excel/CFColors.htm
> > >
> > >
> > > "David McRitchie" wrote:
> > >
> > > > This thread is a parallel of another similar thread (that was actually in two threads), see
> > > >
> > > > http://google.com/groups?threadm=Oyk...TNGP14.phx.gbl
> > > >
> > > > then see
> > > > http://google.com/groups?threadm=%23...TNGP12.phx.gbl
> > > > ---
> > > > HTH,
> > > > David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
> > > > My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
> > > > Search Page: http://www.mvps.org/dmcritchie/excel/search.htm
> > > >
> > > > "dtencza" <(E-Mail Removed)> wrote in message news:(E-Mail Removed)...
> > > > > Thanks, Duke. This looks like just what I need, especially this link:
> > > > >
> > > > > http://www.cpearson.com/excel/CFColors.htm
> > > > >
> > > > > How do I actually use these functions? I copied and pasted these functions
> > > > > into the Visual Basic editor and hit Save. The functions still don't work,
> > > > > though. I get the #NAME? error, which I think is because I didn't put them
> > > > > in there the right way, so Excel doesn't recognize the function yet.
> > > > >
> > > > > I am probably doing something wrong since I haven't tried to do something
> > > > > like this before? Would you (or anyone else) know the right way to get these
> > > > > functions?
> > > > >
> > > > > Thank you for any help you can offer.
> > > > >
> > > > >
> > > > > "Duke Carey" wrote:
> > > > >
> > > > > > See Chip Pearson's site
> > > > > >
> > > > > > http://www.cpearson.com/excel/SortByColor.htm
> > > > > >
> > > > > >
> > > > > > "dtencza" wrote:
> > > > > >
> > > > > > > I have cells in a column, some colored yellow, some not. I am trying to use
> > > > > > > the SUMIF function to sum only the cells in the range that are colored. Can
> > > > > > > this be done? Can I enter something in the "criteria" part of the formula
> > > > > > > that can do this?
> > > > > > >
> > > > > > > =SUMIF(D313,"criteria",D313)
> > > > > > >
> > > > > > > I also tried to use the CELL function's color feature, but I couldn't get it
> > > > > > > to work right. I don't know how to get Excel to recognize if a cell is
> > > > > > > colored in a formula.
> > > > > > > =CELL("color",cell)
> > > > > > >
> > > > > > > It might just be that I don't know what this means in Help:
> > > > > > >
> > > > > > > "color" --> 1 if the cell is formatted in color for negative values;
> > > > > > > otherwise returns 0 (zero).
> > > > > > >
> > > > > > > Can anyone help?
> > > >
> > > >
> > > >

> >
> >
> >



 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

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


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do you print something colored without using the colored ink? =?Utf-8?B?Q29sb3Jjb25mdXNlcg==?= Microsoft Word Document Management 1 15th Dec 2006 02:09 AM
sumif and colored fonts.. =?Utf-8?B?TXVycGg=?= Microsoft Excel Worksheet Functions 1 15th Aug 2005 08:44 PM
How to create a colored ASCII art from a colored graphic image Anonieko Ramos Microsoft C# .NET 0 9th Aug 2004 11:10 AM
Cell right next to colored cells is automatically colored on entering a value Johan De Schutter Microsoft Excel Misc 6 12th Sep 2003 05:31 PM
Cell right next to colored cells is automatically colored on entering a value Johan De Schutter Microsoft Excel Programming 6 12th Sep 2003 05:31 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 01:02 AM.