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

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

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

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"Kim via OfficeKB.com" <[email protected]> wrote in message
news:[email protected]...
> 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

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


"Kim via OfficeKB.com" <[email protected]> wrote in message
news:[email protected]...
> 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]

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" <[email protected]> wrote in message
news:%[email protected]...
>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" <[email protected]> wrote in message
> news:[email protected]...
>> 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
>
>

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


"JulieD" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:%[email protected]...
> >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" <[email protected]> wrote in message
> > news:[email protected]...
> >> 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
> >
> >
>
>

[Kim via 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

[JulieD]

Hi Bob -

yep

(the sumif function looks good) ...

--
Cheers
JulieD

"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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" <[email protected]> wrote in message
>> news:%[email protected]...
>> >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" <[email protected]> wrote in message
>> > news:[email protected]...
>> >> 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]

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" <[email protected]> wrote in message
news:[email protected]...
> 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

[Bob Phillips]

Might add a paragraph on that to the site.

Bob


"JulieD" <[email protected]> wrote in message
news:[email protected]...
> Hi Bob -
>
> yep
>
> (the sumif function looks good) ...
>
> --
> Cheers
> JulieD
>
> "Bob Phillips" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> >> 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" <[email protected]> wrote in message
> >> news:%[email protected]...
> >> >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" <[email protected]> wrote in message
> >> > news:[email protected]...
> >> >> 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]

site's looking v.good BTW

--
Cheers
JulieD

"Bob Phillips" <[email protected]> wrote in message
news:uxpgK%[email protected]...
> Might add a paragraph on that to the site.
>
> Bob
>
>
> "JulieD" <[email protected]> wrote in message
> news:[email protected]...
>> Hi Bob -
>>
>> yep
>>
>> (the sumif function looks good) ...
>>
>> --
>> Cheers
>> JulieD
>>
>> "Bob Phillips" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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" <[email protected]> wrote in message
>> > news:[email protected]...
>> >> 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" <[email protected]> wrote in message
>> >> news:%[email protected]...
>> >> >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" <[email protected]> wrote in message
>> >> > news:[email protected]...
>> >> >> 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
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>>
>>
>
>

[Bob Phillips]

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

Bob


"JulieD" <[email protected]> wrote in message
news:%[email protected]...
> site's looking v.good BTW
>
> --
> Cheers
> JulieD
>
> "Bob Phillips" <[email protected]> wrote in message
> news:uxpgK%[email protected]...
> > Might add a paragraph on that to the site.
> >
> > Bob
> >
> >
> > "JulieD" <[email protected]> wrote in message
> > news:[email protected]...
> >> Hi Bob -
> >>
> >> yep
> >>
> >> (the sumif function looks good) ...
> >>
> >> --
> >> Cheers
> >> JulieD
> >>
> >> "Bob Phillips" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > 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" <[email protected]> wrote in message
> >> > news:[email protected]...
> >> >> 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" <[email protected]> wrote in message
> >> >> news:%[email protected]...
> >> >> >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" <[email protected]> wrote in
message
> >> >> > news:[email protected]...
> >> >> >> 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
> >> >> >
> >> >> >
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >
> >
>
>

[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

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

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

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"Kim via OfficeKB.com" <[email protected]> wrote in message
news:[email protected]...
> 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

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

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"JulieD" <[email protected]> wrote in message
news:%[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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
>
>

[Kim via OfficeKB.com]

hi,

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

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

[JulieD]

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" <[email protected]> wrote in message
news:[email protected]...
> hi,
>
> it should be 4. all of the v values need to be added together.
>
> --
> Message posted via http://www.officekb.com