|
SUMIF cell is colored
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? |
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? |
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? |
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? |
This thread is a parallel of another similar thread (that was actually in two threads), see
http://google.com/groups?threadm=Oyk...GP14.phx .gbl then see http://google.com/groups?threadm=%23...GP12.p hx.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" wrote in message ... 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? |
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...GP14.phx .gbl then see http://google.com/groups?threadm=%23...GP12.p hx.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" wrote in message ... 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? |
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 ... 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...GP14.phx .gbl then see http://google.com/groups?threadm=%23...GP12.p hx.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" wrote in message ... 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? |
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 ... 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...GP14.phx .gbl then see http://google.com/groups?threadm=%23...GP12.p hx.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" wrote in message ... 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? |
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 ... 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 ... 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...GP14.phx .gbl then see http://google.com/groups?threadm=%23...GP12.p hx.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" wrote in message ... 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? |
| All times are GMT +1. The time now is 07:41 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com