sum by cell color

  • Thread starter Kim via OfficeKB.com
  • Start date
K

Kim via OfficeKB.com

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
 
J

JulieD

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???
 
J

JulieD

Hi Bob

looks good, however, i can't see how you can use the SUMPRODUCT function if
SOME of the cells contain alpha prefixes ...
 
B

Bob Phillips

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)
 
K

Kim via OfficeKB.com

yes, all of the cells will be in the format of a letter next to a number
(ie: V3)
 
J

JulieD

Hi Bob -

yep :)

(the sumif function looks good) ...

--
Cheers
JulieD

Bob Phillips said:
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)
Click to expand...
 
J

JulieD

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.
 
K

Kim via OfficeKB.com

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
 
J

JulieD

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.
 
J

JulieD

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?
 
Top