|
SUMIF using color
Is there a way to only add cells in a column that have a certain colored
font? I looked at the link at http://www.cpearson.com/excel/colors.htm but I couldn't figure out if this can be done or not. What I need specifically is a way to say: add G100 plus any numbers that are blue from G1:G99. Thanks in advance, Kris |
SUMIF using color
I think I may have just found the answer to my question at
http://www.xldynamic.com/source/xld.ColourCounter.html Thanks "Klee" wrote: Is there a way to only add cells in a column that have a certain colored font? I looked at the link at http://www.cpearson.com/excel/colors.htm but I couldn't figure out if this can be done or not. What I need specifically is a way to say: add G100 plus any numbers that are blue from G1:G99. Thanks in advance, Kris |
SUMIF using color
See http://www.xldynamic.com/source/xld.ColourCounter.html for a working
solution -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Klee" wrote in message ... Is there a way to only add cells in a column that have a certain colored font? I looked at the link at http://www.cpearson.com/excel/colors.htm but I couldn't figure out if this can be done or not. What I need specifically is a way to say: add G100 plus any numbers that are blue from G1:G99. Thanks in advance, Kris |
SUMIF using color
Yes it is possible, either use the info on Chip's site or this.
Regardless it involves VBA http://www.xldynamic.com/source/xld.ColourCounter.html -- Regards, Peo Sjoblom "Klee" wrote in message ... Is there a way to only add cells in a column that have a certain colored font? I looked at the link at http://www.cpearson.com/excel/colors.htm but I couldn't figure out if this can be done or not. What I need specifically is a way to say: add G100 plus any numbers that are blue from G1:G99. Thanks in advance, Kris |
SUMIF using color
Hi Bob,
I went to the site and copied the code at the bottom. I went into toolsmacrosVBA then into InsertModuleand pased the code. Then I used the following formula: =SUMPRODUCT(--(ColorIndex(f3:f74,true)=6),f3:f74) When I hit enter nothing happens. The formula is still just written in the cell. When I evaluate the formula it just says "the cell currently being evaluated contains a constant". I have no idea what I'm doing wrong as this is way beyond my excel skills. Do you have any thoughts? "Bob Phillips" wrote: See http://www.xldynamic.com/source/xld.ColourCounter.html for a working solution -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Klee" wrote in message ... Is there a way to only add cells in a column that have a certain colored font? I looked at the link at http://www.cpearson.com/excel/colors.htm but I couldn't figure out if this can be done or not. What I need specifically is a way to say: add G100 plus any numbers that are blue from G1:G99. Thanks in advance, Kris |
SUMIF using color
Assuming that you have copied the ColorIndex function in correctly, the
formula should work. I have just reconstructed it and it works fine. You appreciate that your formula is summing the yellow NUMBERS in F3:F74? -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Klee" wrote in message ... Hi Bob, I went to the site and copied the code at the bottom. I went into toolsmacrosVBA then into InsertModuleand pased the code. Then I used the following formula: =SUMPRODUCT(--(ColorIndex(f3:f74,true)=6),f3:f74) When I hit enter nothing happens. The formula is still just written in the cell. When I evaluate the formula it just says "the cell currently being evaluated contains a constant". I have no idea what I'm doing wrong as this is way beyond my excel skills. Do you have any thoughts? "Bob Phillips" wrote: See http://www.xldynamic.com/source/xld.ColourCounter.html for a working solution -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Klee" wrote in message ... Is there a way to only add cells in a column that have a certain colored font? I looked at the link at http://www.cpearson.com/excel/colors.htm but I couldn't figure out if this can be done or not. What I need specifically is a way to say: add G100 plus any numbers that are blue from G1:G99. Thanks in advance, Kris |
SUMIF using color
Thanks so much for all of your help. I don't know what I was doing wrong but
it was driving me crazy so I gave up and went with the helper column to add the colored cells. Thanks again. Kris "Bob Phillips" wrote: Assuming that you have copied the ColorIndex function in correctly, the formula should work. I have just reconstructed it and it works fine. You appreciate that your formula is summing the yellow NUMBERS in F3:F74? -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Klee" wrote in message ... Hi Bob, I went to the site and copied the code at the bottom. I went into toolsmacrosVBA then into InsertModuleand pased the code. Then I used the following formula: =SUMPRODUCT(--(ColorIndex(f3:f74,true)=6),f3:f74) When I hit enter nothing happens. The formula is still just written in the cell. When I evaluate the formula it just says "the cell currently being evaluated contains a constant". I have no idea what I'm doing wrong as this is way beyond my excel skills. Do you have any thoughts? "Bob Phillips" wrote: See http://www.xldynamic.com/source/xld.ColourCounter.html for a working solution -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Klee" wrote in message ... Is there a way to only add cells in a column that have a certain colored font? I looked at the link at http://www.cpearson.com/excel/colors.htm but I couldn't figure out if this can be done or not. What I need specifically is a way to say: add G100 plus any numbers that are blue from G1:G99. Thanks in advance, Kris |
SUMIF using color
As I said to you, because you set the text argument to TRUE in the
ColorIndex function call, you were not testing coloured cells, but coloured text within those cells!!! -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Klee" wrote in message ... Thanks so much for all of your help. I don't know what I was doing wrong but it was driving me crazy so I gave up and went with the helper column to add the colored cells. Thanks again. Kris "Bob Phillips" wrote: Assuming that you have copied the ColorIndex function in correctly, the formula should work. I have just reconstructed it and it works fine. You appreciate that your formula is summing the yellow NUMBERS in F3:F74? -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Klee" wrote in message ... Hi Bob, I went to the site and copied the code at the bottom. I went into toolsmacrosVBA then into InsertModuleand pased the code. Then I used the following formula: =SUMPRODUCT(--(ColorIndex(f3:f74,true)=6),f3:f74) When I hit enter nothing happens. The formula is still just written in the cell. When I evaluate the formula it just says "the cell currently being evaluated contains a constant". I have no idea what I'm doing wrong as this is way beyond my excel skills. Do you have any thoughts? "Bob Phillips" wrote: See http://www.xldynamic.com/source/xld.ColourCounter.html for a working solution -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Klee" wrote in message ... Is there a way to only add cells in a column that have a certain colored font? I looked at the link at http://www.cpearson.com/excel/colors.htm but I couldn't figure out if this can be done or not. What I need specifically is a way to say: add G100 plus any numbers that are blue from G1:G99. Thanks in advance, Kris |
SUMIF using color
Oh, Now I get it, thanks!
"Bob Phillips" wrote: As I said to you, because you set the text argument to TRUE in the ColorIndex function call, you were not testing coloured cells, but coloured text within those cells!!! -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Klee" wrote in message ... Thanks so much for all of your help. I don't know what I was doing wrong but it was driving me crazy so I gave up and went with the helper column to add the colored cells. Thanks again. Kris "Bob Phillips" wrote: Assuming that you have copied the ColorIndex function in correctly, the formula should work. I have just reconstructed it and it works fine. You appreciate that your formula is summing the yellow NUMBERS in F3:F74? -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Klee" wrote in message ... Hi Bob, I went to the site and copied the code at the bottom. I went into toolsmacrosVBA then into InsertModuleand pased the code. Then I used the following formula: =SUMPRODUCT(--(ColorIndex(f3:f74,true)=6),f3:f74) When I hit enter nothing happens. The formula is still just written in the cell. When I evaluate the formula it just says "the cell currently being evaluated contains a constant". I have no idea what I'm doing wrong as this is way beyond my excel skills. Do you have any thoughts? "Bob Phillips" wrote: See http://www.xldynamic.com/source/xld.ColourCounter.html for a working solution -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Klee" wrote in message ... Is there a way to only add cells in a column that have a certain colored font? I looked at the link at http://www.cpearson.com/excel/colors.htm but I couldn't figure out if this can be done or not. What I need specifically is a way to say: add G100 plus any numbers that are blue from G1:G99. Thanks in advance, Kris |
SUMIF using color
Yep. You need to copy that code into the VBE for that workbook.
Since you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm mshaw1515 wrote: I need a totally idiot proof guide to doing this. I tried pasting '=SUMPRODUCT(--(ColorIndex(A1:A100)=3))' into a cell and chanign the 3 to 6 which i beleive value for yellow. All I get is #NAME?. Does this work in Excel 2007? Do i need to enable something? Do i need to paste this code into VBL? Any help would be GREATLY appreciated! "Bob Phillips" wrote: See http://www.xldynamic.com/source/xld.ColourCounter.html for a working solution -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Klee" wrote in message ... Is there a way to only add cells in a column that have a certain colored font? I looked at the link at http://www.cpearson.com/excel/colors.htm but I couldn't figure out if this can be done or not. What I need specifically is a way to say: add G100 plus any numbers that are blue from G1:G99. Thanks in advance, Kris -- Dave Peterson |
SUMIF using color
I need a totally idiot proof guide to doing this. I tried pasting
'=SUMPRODUCT(--(ColorIndex(A1:A100)=3))' into a cell and chanign the 3 to 6 which i beleive value for yellow. All I get is #NAME?. Does this work in Excel 2007? Do i need to enable something? Do i need to paste this code into VBL? Any help would be GREATLY appreciated! "Bob Phillips" wrote: See http://www.xldynamic.com/source/xld.ColourCounter.html for a working solution -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Klee" wrote in message ... Is there a way to only add cells in a column that have a certain colored font? I looked at the link at http://www.cpearson.com/excel/colors.htm but I couldn't figure out if this can be done or not. What I need specifically is a way to say: add G100 plus any numbers that are blue from G1:G99. Thanks in advance, Kris |
SumIf by colors
This is a Romanian version of SumIf by Colors.
http://valygreavu.wordpress.com/2010...ful-solutions/ mshaw151 wrote: I need a totally idiot proof guide to doing this. 07-Dec-07 I need a totally idiot proof guide to doing this. I tried pasting '=SUMPRODUCT(--(ColorIndex(A1:A100)=3))' into a cell and chanign the 3 to 6 which i beleive value for yellow. All I get is #NAME?. Does this work in Excel 2007? Do i need to enable something? Do i need to paste this code into VBL? Any help would be GREATLY appreciated! "Bob Phillips" wrote: Previous Posts In This Thread: On Friday, July 13, 2007 1:18 PM Kle wrote: SUMIF using color Is there a way to only add cells in a column that have a certain colored font? I looked at the link at http://www.cpearson.com/excel/colors.htm but I couldn't figure out if this can be done or not. What I need specifically is a way to say: add G100 plus any numbers that are blue from G1:G99. Thanks in advance, Kris On Friday, July 13, 2007 1:24 PM Kle wrote: SUMIF using color I think I may have just found the answer to my question at http://www.xldynamic.com/source/xld.ColourCounter.html Thanks "Klee" wrote: On Friday, July 13, 2007 1:24 PM Bob Phillips wrote: SUMIF using color See http://www.xldynamic.com/source/xld.ColourCounter.html for a working solution -- --- HTH Bob (there is no email, no snail mail, but somewhere should be gmail in my addy) On Friday, July 13, 2007 1:24 PM Peo Sjoblom wrote: Yes it is possible, either use the info on Chip's site or this. Yes it is possible, either use the info on Chip's site or this. Regardless it involves VBA http://www.xldynamic.com/source/xld.ColourCounter.html -- Regards, Peo Sjoblom On Friday, July 13, 2007 1:54 PM Kle wrote: Hi Bob,I went to the site and copied the code at the bottom. Hi Bob, I went to the site and copied the code at the bottom. I went into toolsmacrosVBA then into InsertModuleand pased the code. Then I used the following formula: =SUMPRODUCT(--(ColorIndex(f3:f74,true)=6),f3:f74) When I hit enter nothing happens. The formula is still just written in the cell. When I evaluate the formula it just says "the cell currently being evaluated contains a constant". I have no idea what I'm doing wrong as this is way beyond my excel skills. Do you have any thoughts? "Bob Phillips" wrote: On Friday, July 13, 2007 5:43 PM Bob Phillips wrote: Assuming that you have copied the ColorIndex function in correctly, the Assuming that you have copied the ColorIndex function in correctly, the formula should work. I have just reconstructed it and it works fine. You appreciate that your formula is summing the yellow NUMBERS in F3:F74? -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Klee" wrote in message ... On Friday, July 13, 2007 6:44 PM Kle wrote: Thanks so much for all of your help. Thanks so much for all of your help. I don't know what I was doing wrong but it was driving me crazy so I gave up and went with the helper column to add the colored cells. Thanks again. Kris "Bob Phillips" wrote: On Saturday, July 14, 2007 10:26 AM Bob Phillips wrote: As I said to you, because you set the text argument to TRUE in the ColorIndex As I said to you, because you set the text argument to TRUE in the ColorIndex function call, you were not testing coloured cells, but coloured text within those cells!!! -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Klee" wrote in message ... On Saturday, July 14, 2007 2:50 PM Kle wrote: SUMIF using color Oh, Now I get it, thanks! "Bob Phillips" wrote: On Friday, December 07, 2007 5:04 AM Dave Peterson wrote: Yep. You need to copy that code into the VBE for that workbook. Yep. You need to copy that code into the VBE for that workbook. Since you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm mshaw1515 wrote: -- Dave Peterson On Friday, December 07, 2007 2:15 PM mshaw151 wrote: I need a totally idiot proof guide to doing this. I need a totally idiot proof guide to doing this. I tried pasting '=SUMPRODUCT(--(ColorIndex(A1:A100)=3))' into a cell and chanign the 3 to 6 which i beleive value for yellow. All I get is #NAME?. Does this work in Excel 2007? Do i need to enable something? Do i need to paste this code into VBL? Any help would be GREATLY appreciated! "Bob Phillips" wrote: Submitted via EggHeadCafe - Software Developer Portal of Choice WebClient Class: Gotchas and Basics http://www.eggheadcafe.com/tutorials...s-gotchas.aspx |
| All times are GMT +1. The time now is 03:03 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com