How do you count cells with background color yellow?

Can anyone tell me a formula that will count the number of yellow cells in a
range.
I used =countif(AV2:HM2,"interior.colorindex = 36"). I don't get a error
message. I get "2", when it shoudl be 3 -- no matter how many yellow cells
there are, I always get "2". What am I doing wrong? sd

There is no built in function to count colours of cells, no formula will do
it. It can be done though using code, have a look at this on Chip Pearsons
site,
http://www.cpearson.com/excel/colors.htm
Regards,
Alan,
"Stephanie D" <Stephanie [email protected]> wrote in message
news:[email protected]...
> Can anyone tell me a formula that will count the number of yellow cells in
> a
> range.
> I used =countif(AV2:HM2,"interior.colorindex = 36"). I don't get a error
> message. I get "2", when it shoudl be 3 -- no matter how many yellow cells
> there are, I always get "2". What am I doing wrong? sd

See http://www.xldynamic.com/source/xld.ColourCounter.html

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Stephanie D" <Stephanie [email protected]> wrote in message
news:[email protected]...
> Can anyone tell me a formula that will count the number of yellow cells in
a
> range.
> I used =countif(AV2:HM2,"interior.colorindex = 36"). I don't get a error
> message. I get "2", when it shoudl be 3 -- no matter how many yellow cells
> there are, I always get "2". What am I doing wrong? sd

Thanks Alan. This is exactly what I need. VBA coding is not my specialty,
but I will try it. sd

"Alan" wrote:

> There is no built in function to count colours of cells, no formula will do
> it. It can be done though using code, have a look at this on Chip Pearsons
> site,
> http://www.cpearson.com/excel/colors.htm
> Regards,
> Alan,
> "Stephanie D" <Stephanie [email protected]> wrote in message
> news:[email protected]...
> > Can anyone tell me a formula that will count the number of yellow cells in
> > a
> > range.
> > I used =countif(AV2:HM2,"interior.colorindex = 36"). I don't get a error
> > message. I get "2", when it shoudl be 3 -- no matter how many yellow cells
> > there are, I always get "2". What am I doing wrong? sd
>
>
>

The link that Bob posted seems excellent, I haven't seen that one before,
but this is really not an exact science!
The major problem it would appear is that the changing of the colour of the
cell does not constitute a change in the value of a cell in Excel and
therefore cannot trigger a macro, and does not trigger a recalculation
automatically, however using the format painter appears to do so.
Unless you code it to force a recalculation each time the colour is changed
it could easily give misleading results,
Regards,
Alan.
"Stephanie D" <[email protected]> wrote in message
news:[email protected]...
> Thanks Alan. This is exactly what I need. VBA coding is not my
> specialty,
> but I will try it. sd
>
> "Alan" wrote:
>
>> There is no built in function to count colours of cells, no formula will
>> do
>> it. It can be done though using code, have a look at this on Chip
>> Pearsons
>> site,
>> http://www.cpearson.com/excel/colors.htm
>> Regards,
>> Alan,
>> "Stephanie D" <Stephanie [email protected]> wrote in message
>> news:[email protected]...
>> > Can anyone tell me a formula that will count the number of yellow cells
>> > in
>> > a
>> > range.
>> > I used =countif(AV2:HM2,"interior.colorindex = 36"). I don't get a
>> > error
>> > message. I get "2", when it shoudl be 3 -- no matter how many yellow
>> > cells
>> > there are, I always get "2". What am I doing wrong? sd
>>
>>
>>

Well the only thing I want to do is count the number of cells in a range
where the interior background color is yellow. No need to recalculate the
data in the cell nor change the color. Thanks a lot. sd

"Alan" wrote:

> The link that Bob posted seems excellent, I haven't seen that one before,
> but this is really not an exact science!
> The major problem it would appear is that the changing of the colour of the
> cell does not constitute a change in the value of a cell in Excel and
> therefore cannot trigger a macro, and does not trigger a recalculation
> automatically, however using the format painter appears to do so.
> Unless you code it to force a recalculation each time the colour is changed
> it could easily give misleading results,
> Regards,
> Alan.
> "Stephanie D" <[email protected]> wrote in message
> news:[email protected]...
> > Thanks Alan. This is exactly what I need. VBA coding is not my
> > specialty,
> > but I will try it. sd
> >
> > "Alan" wrote:
> >
> >> There is no built in function to count colours of cells, no formula will
> >> do
> >> it. It can be done though using code, have a look at this on Chip
> >> Pearsons
> >> site,
> >> http://www.cpearson.com/excel/colors.htm
> >> Regards,
> >> Alan,
> >> "Stephanie D" <Stephanie [email protected]> wrote in message
> >> news:[email protected]...
> >> > Can anyone tell me a formula that will count the number of yellow cells
> >> > in
> >> > a
> >> > range.
> >> > I used =countif(AV2:HM2,"interior.colorindex = 36"). I don't get a
> >> > error
> >> > message. I get "2", when it shoudl be 3 -- no matter how many yellow
> >> > cells
> >> > there are, I always get "2". What am I doing wrong? sd
> >>
> >>
> >>
>
>
>

Thanks Bob. This one is clearer. sd

"Bob Phillips" wrote:

> See http://www.xldynamic.com/source/xld.ColourCounter.html
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Stephanie D" <Stephanie [email protected]> wrote in message
> news:[email protected]...
> > Can anyone tell me a formula that will count the number of yellow cells in
> a
> > range.
> > I used =countif(AV2:HM2,"interior.colorindex = 36"). I don't get a error
> > message. I get "2", when it shoudl be 3 -- no matter how many yellow cells
> > there are, I always get "2". What am I doing wrong? sd
>
>
>