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

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

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

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

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

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

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

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

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

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

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

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

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

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

hi,

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

--
Message posted via http://www.officekb.com

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