sum by cell color

[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

 

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

 

[Bob Phillips]

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)

 

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

 

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

 

[Kim via OfficeKB.com]

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

 

[JulieD]

Hi Bob -

yep

(the sumif function looks good) ...

--
Cheers
JulieD

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]

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.

 

[Bob Phillips]

Might add a paragraph on that to the site.

Bob

Hi Bob -

yep

(the sumif function looks good) ...

 

[JulieD]

site's looking v.good BTW

--
Cheers
JulieD

Might add a paragraph on that to the site.

Bob

 

[Bob Phillips]

Thank-you, it is need of me finishing about a dozen topics still o/s.

Bob

 

[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

 

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

 

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

 

[Kim via OfficeKB.com]

hi,

it should be 4. all of the v values need to be added together.

 

[JulieD]

Hi Kim

it's on its way back to you