SUMIF cell is colored

Discussion in 'Microsoft Excel Misc' started by Guest, Aug 17, 2005.

  1. Guest

    Guest Guest

    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(D3:D13,"criteria",D3:D13)

    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?
     
    Guest, Aug 17, 2005
    #1
    1. Advertisements

  2. Guest

    Guest Guest

    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(D3:D13,"criteria",D3:D13)
    >
    > 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?
     
    Guest, Aug 17, 2005
    #2
    1. Advertisements

  3. Guest

    Guest Guest

    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(D3:D13,"criteria",D3:D13)
    >
    > 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?
     
    Guest, Aug 17, 2005
    #3
  4. Guest

    Guest Guest

    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(D3:D13,"criteria",D3:D13)
    > >
    > > 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?
     
    Guest, Aug 17, 2005
    #4
  5. This thread is a parallel of another similar thread (that was actually in two threads), see

    http://google.com/groups?threadm=

    then see
    http://google.com/groups?threadm=#
    ---
    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" <> wrote in message news:...
    > 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(D3:D13,"criteria",D3:D13)
    > > >
    > > > 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?
     
    David McRitchie, Aug 17, 2005
    #5
  6. Guest

    Guest Guest

    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=
    >
    > then see
    > http://google.com/groups?threadm=#
    > ---
    > 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" <> wrote in message news:...
    > > 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(D3:D13,"criteria",D3:D13)
    > > > >
    > > > > 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?

    >
    >
    >
     
    Guest, Aug 18, 2005
    #6
  7. 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" <> wrote in message news:...
    > 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=
    > >
    > > then see
    > > http://google.com/groups?threadm=#
    > > ---
    > > 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" <> wrote in message news:...
    > > > 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(D3:D13,"criteria",D3:D13)
    > > > > >
    > > > > > 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?

    > >
    > >
    > >
     
    David McRitchie, Aug 18, 2005
    #7
  8. Guest

    Guest Guest

    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" <> wrote in message news:...
    > > 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=
    > > >
    > > > then see
    > > > http://google.com/groups?threadm=#
    > > > ---
    > > > 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" <> wrote in message news:...
    > > > > 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(D3:D13,"criteria",D3:D13)
    > > > > > >
    > > > > > > 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?
    > > >
    > > >
    > > >

    >
    >
    >
     
    Guest, Aug 18, 2005
    #8
  9. Sorry about that, left out the excel directory, the correct link is
    http://www.mvps.org/dmcritchie/excel/code/xl2htmlx.txt


    "dtencza" <> wrote in message news:...
    > 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" <> wrote in message news:...
    > > > 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=
    > > > >
    > > > > then see
    > > > > http://google.com/groups?threadm=#
    > > > > ---
    > > > > 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" <> wrote in message news:...
    > > > > > 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(D3:D13,"criteria",D3:D13)
    > > > > > > >
    > > > > > > > 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?
    > > > >
    > > > >
    > > > >

    > >
    > >
    > >
     
    David McRitchie, Aug 18, 2005
    #9
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Johan De Schutter
    Replies:
    6
    Views:
    369
    J.E. McGimpsey
    Sep 12, 2003
  2. alowicious

    finding and extracting values from a specific colored cell

    alowicious, Jun 12, 2004, in forum: Microsoft Excel Misc
    Replies:
    5
    Views:
    168
    alowicious
    Jun 16, 2004
  3. Gerbatin

    linking colored cell

    Gerbatin, Oct 12, 2004, in forum: Microsoft Excel Misc
    Replies:
    1
    Views:
    114
    Gord Dibben
    Oct 13, 2004
  4. rick
    Replies:
    2
    Views:
    318
    Pete_UK
    Sep 3, 2008
  5. abhilash01
    Replies:
    1
    Views:
    144
Loading...

Share This Page