Home |
Search |
Today's Posts |
#1
|
|||
|
|||
sum by cell color
Hi,
I need help creating a macro. I need to sum the values of cells which are a certain color. The catch is that the values also contain letters. The cells can contain values such as V1 or V.25. So, for an example, I would like to sum the numbers in all the purple cells. Any suggestions on how to accomplish this would be great. Thanks! -*k -- Message posted via http://www.officekb.com |
#2
|
|||
|
|||
Hi Kim
there is no inbuilt function in excel to sum by colour .. you need to use code to do this ... check out http://www.cpearson.com/excel/colors.htm for details. you will then have to add into the code functionality to extract the numbers from the text ... and how this is done depends on the contents of the cells ... you gave two examples both with a leading "V" is this how all the text/number cells look or??? -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ....well i'm working on it anyway "Kim via OfficeKB.com" wrote in message ... Hi, I need help creating a macro. I need to sum the values of cells which are a certain color. The catch is that the values also contain letters. The cells can contain values such as V1 or V.25. So, for an example, I would like to sum the numbers in all the purple cells. Any suggestions on how to accomplish this would be great. Thanks! -*k -- Message posted via http://www.officekb.com |
#3
|
|||
|
|||
A full solution can be found at
http://www.xldynamic.com/source/xld.ColourCounter.html -- HTH RP (remove nothere from the email address if mailing direct) "Kim via OfficeKB.com" wrote in message ... Hi, I need help creating a macro. I need to sum the values of cells which are a certain color. The catch is that the values also contain letters. The cells can contain values such as V1 or V.25. So, for an example, I would like to sum the numbers in all the purple cells. Any suggestions on how to accomplish this would be great. Thanks! -*k -- Message posted via http://www.officekb.com |
#4
|
|||
|
|||
Hi Bob
looks good, however, i can't see how you can use the SUMPRODUCT function if SOME of the cells contain alpha prefixes ... -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ....well i'm working on it anyway "Bob Phillips" wrote in message ... A full solution can be found at http://www.xldynamic.com/source/xld.ColourCounter.html -- HTH RP (remove nothere from the email address if mailing direct) "Kim via OfficeKB.com" wrote in message ... Hi, I need help creating a macro. I need to sum the values of cells which are a certain color. The catch is that the values also contain letters. The cells can contain values such as V1 or V.25. So, for an example, I would like to sum the numbers in all the purple cells. Any suggestions on how to accomplish this would be great. Thanks! -*k -- Message posted via http://www.officekb.com |
#5
|
|||
|
|||
JulieD,
You are pedantic <vbg. Okay so, not full. Haven't got a SUMPRODUCT formula to work (yet), but I have a SUM formula that can be utilised, =SUM(IF(ColorIndex(A1:A100)=3,IF(A1:A100<"",--RIGHT(A1:A100,LEN(A1:A100)-1) ,0),0)) which is an array formula, so commit with Ctrl-Shift-Enter. All the caveats on that page about no automatic recalculation still hold. -- HTH RP (remove nothere from the email address if mailing direct) "JulieD" wrote in message ... Hi Bob looks good, however, i can't see how you can use the SUMPRODUCT function if SOME of the cells contain alpha prefixes ... -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ...well i'm working on it anyway "Bob Phillips" wrote in message ... A full solution can be found at http://www.xldynamic.com/source/xld.ColourCounter.html -- HTH RP (remove nothere from the email address if mailing direct) "Kim via OfficeKB.com" wrote in message ... Hi, I need help creating a macro. I need to sum the values of cells which are a certain color. The catch is that the values also contain letters. The cells can contain values such as V1 or V.25. So, for an example, I would like to sum the numbers in all the purple cells. Any suggestions on how to accomplish this would be great. Thanks! -*k -- Message posted via http://www.officekb.com |
#6
|
|||
|
|||
yes, all of the cells will be in the format of a letter next to a number
(ie: V3) -- Message posted via http://www.officekb.com |
#7
|
|||
|
|||
Hi Bob -
yep :) (the sumif function looks good) ... -- Cheers JulieD "Bob Phillips" wrote in message ... JulieD, You are pedantic <vbg. Okay so, not full. Haven't got a SUMPRODUCT formula to work (yet), but I have a SUM formula that can be utilised, =SUM(IF(ColorIndex(A1:A100)=3,IF(A1:A100<"",--RIGHT(A1:A100,LEN(A1:A100)-1) ,0),0)) which is an array formula, so commit with Ctrl-Shift-Enter. All the caveats on that page about no automatic recalculation still hold. -- HTH RP (remove nothere from the email address if mailing direct) "JulieD" wrote in message ... Hi Bob looks good, however, i can't see how you can use the SUMPRODUCT function if SOME of the cells contain alpha prefixes ... -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ...well i'm working on it anyway "Bob Phillips" wrote in message ... A full solution can be found at http://www.xldynamic.com/source/xld.ColourCounter.html -- HTH RP (remove nothere from the email address if mailing direct) "Kim via OfficeKB.com" wrote in message ... Hi, I need help creating a macro. I need to sum the values of cells which are a certain color. The catch is that the values also contain letters. The cells can contain values such as V1 or V.25. So, for an example, I would like to sum the numbers in all the purple cells. Any suggestions on how to accomplish this would be great. Thanks! -*k -- Message posted via http://www.officekb.com |
#8
|
|||
|
|||
Hi Kim
if you have a look at Bob's original post and use the code from his website, you can then use the formula he posted in this second post to give you what you want. if you need more asssistance implementing this, please post back. -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ....well i'm working on it anyway "Kim via OfficeKB.com" wrote in message ... yes, all of the cells will be in the format of a letter next to a number (ie: V3) -- Message posted via http://www.officekb.com |
#9
|
|||
|
|||
Might add a paragraph on that to the site.
Bob "JulieD" wrote in message ... Hi Bob - yep :) (the sumif function looks good) ... -- Cheers JulieD "Bob Phillips" wrote in message ... JulieD, You are pedantic <vbg. Okay so, not full. Haven't got a SUMPRODUCT formula to work (yet), but I have a SUM formula that can be utilised, =SUM(IF(ColorIndex(A1:A100)=3,IF(A1:A100<"",--RIGHT(A1:A100,LEN(A1:A100)-1) ,0),0)) which is an array formula, so commit with Ctrl-Shift-Enter. All the caveats on that page about no automatic recalculation still hold. -- HTH RP (remove nothere from the email address if mailing direct) "JulieD" wrote in message ... Hi Bob looks good, however, i can't see how you can use the SUMPRODUCT function if SOME of the cells contain alpha prefixes ... -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ...well i'm working on it anyway "Bob Phillips" wrote in message ... A full solution can be found at http://www.xldynamic.com/source/xld.ColourCounter.html -- HTH RP (remove nothere from the email address if mailing direct) "Kim via OfficeKB.com" wrote in message ... Hi, I need help creating a macro. I need to sum the values of cells which are a certain color. The catch is that the values also contain letters. The cells can contain values such as V1 or V.25. So, for an example, I would like to sum the numbers in all the purple cells. Any suggestions on how to accomplish this would be great. Thanks! -*k -- Message posted via http://www.officekb.com |
#10
|
|||
|
|||
site's looking v.good BTW
-- Cheers JulieD "Bob Phillips" wrote in message ... Might add a paragraph on that to the site. Bob "JulieD" wrote in message ... Hi Bob - yep :) (the sumif function looks good) ... -- Cheers JulieD "Bob Phillips" wrote in message ... JulieD, You are pedantic <vbg. Okay so, not full. Haven't got a SUMPRODUCT formula to work (yet), but I have a SUM formula that can be utilised, =SUM(IF(ColorIndex(A1:A100)=3,IF(A1:A100<"",--RIGHT(A1:A100,LEN(A1:A100)-1) ,0),0)) which is an array formula, so commit with Ctrl-Shift-Enter. All the caveats on that page about no automatic recalculation still hold. -- HTH RP (remove nothere from the email address if mailing direct) "JulieD" wrote in message ... Hi Bob looks good, however, i can't see how you can use the SUMPRODUCT function if SOME of the cells contain alpha prefixes ... -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ...well i'm working on it anyway "Bob Phillips" wrote in message ... A full solution can be found at http://www.xldynamic.com/source/xld.ColourCounter.html -- HTH RP (remove nothere from the email address if mailing direct) "Kim via OfficeKB.com" wrote in message ... Hi, I need help creating a macro. I need to sum the values of cells which are a certain color. The catch is that the values also contain letters. The cells can contain values such as V1 or V.25. So, for an example, I would like to sum the numbers in all the purple cells. Any suggestions on how to accomplish this would be great. Thanks! -*k -- Message posted via http://www.officekb.com |
#11
|
|||
|
|||
Thank-you, it is need of me finishing about a dozen topics still o/s.
Bob "JulieD" wrote in message ... site's looking v.good BTW -- Cheers JulieD "Bob Phillips" wrote in message ... Might add a paragraph on that to the site. Bob "JulieD" wrote in message ... Hi Bob - yep :) (the sumif function looks good) ... -- Cheers JulieD "Bob Phillips" wrote in message ... JulieD, You are pedantic <vbg. Okay so, not full. Haven't got a SUMPRODUCT formula to work (yet), but I have a SUM formula that can be utilised, =SUM(IF(ColorIndex(A1:A100)=3,IF(A1:A100<"",--RIGHT(A1:A100,LEN(A1:A100)-1) ,0),0)) which is an array formula, so commit with Ctrl-Shift-Enter. All the caveats on that page about no automatic recalculation still hold. -- HTH RP (remove nothere from the email address if mailing direct) "JulieD" wrote in message ... Hi Bob looks good, however, i can't see how you can use the SUMPRODUCT function if SOME of the cells contain alpha prefixes ... -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ...well i'm working on it anyway "Bob Phillips" wrote in message ... A full solution can be found at http://www.xldynamic.com/source/xld.ColourCounter.html -- HTH RP (remove nothere from the email address if mailing direct) "Kim via OfficeKB.com" wrote in message ... Hi, I need help creating a macro. I need to sum the values of cells which are a certain color. The catch is that the values also contain letters. The cells can contain values such as V1 or V.25. So, for an example, I would like to sum the numbers in all the purple cells. Any suggestions on how to accomplish this would be great. Thanks! -*k -- Message posted via http://www.officekb.com |
#12
|
|||
|
|||
Hi Julie,
I entered the code and used the formula, but the only value I get is 0 (which is wrong). Any suggestions? Would it help if I emailed you a sample of what I'm trying to accomplish? Thanks, *-k -- Message posted via http://www.officekb.com |
#13
|
|||
|
|||
Hi Kim
yes, email me your workbook (it's now midnight here so i won't get to it until tomorrow) ... use julied_ng at hcts dot net dot au as the email address. -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ....well i'm working on it anyway "Kim via OfficeKB.com" wrote in message ... Hi Julie, I entered the code and used the formula, but the only value I get is 0 (which is wrong). Any suggestions? Would it help if I emailed you a sample of what I'm trying to accomplish? Thanks, *-k -- Message posted via http://www.officekb.com |
#14
|
|||
|
|||
Hi Kim
got the workbook, if you have V3 in a cell and V1 in another cell is the answer you're looking for 4 or 2? -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ....well i'm working on it anyway "JulieD" wrote in message ... Hi Kim yes, email me your workbook (it's now midnight here so i won't get to it until tomorrow) ... use julied_ng at hcts dot net dot au as the email address. -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ...well i'm working on it anyway "Kim via OfficeKB.com" wrote in message ... Hi Julie, I entered the code and used the formula, but the only value I get is 0 (which is wrong). Any suggestions? Would it help if I emailed you a sample of what I'm trying to accomplish? Thanks, *-k -- Message posted via http://www.officekb.com |
#15
|
|||
|
|||
hi,
it should be 4. all of the v values need to be added together. -- Message posted via http://www.officekb.com |
#16
|
|||
|
|||
Hi Kim
it's on its way back to you -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ....well i'm working on it anyway "Kim via OfficeKB.com" wrote in message ... hi, it should be 4. all of the v values need to be added together. -- Message posted via http://www.officekb.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel formulas based upon the color shading of a cell? | Excel Worksheet Functions | |||
How bring a color on cell from one sheet to another | Excel Discussion (Misc queries) | |||
The color in excel does not show up in the cell on screen | Excel Discussion (Misc queries) | |||
How do I connect cell color to bar chart color? | Charts and Charting in Excel | |||
Current Cell Color | Excel Worksheet Functions |