Is it possible to combine countif and countcolor?
 

I have a range of cells and I need to count how many
have a blue background and a cell value of "C" and how
many have a green background and a cell value of "C"

I can count all the cells in the range that are blue or
green and I can count all the cells that contain the
value of "C", but how can I count the combined
criteria?

I'm thinking I just need some algebraic help... :-S

Is it possible to combine countif and countcolor?
 

Suppose you are counting the "C" values with
If thisCell.value = "C" then
Kount = Kount +1
End If
.....

and you count Green cells with

If thisCell.Font.ColorIndex = CI 'where CI is the index value
for your shade of green
Kount = Fount +1

The do one of these:

A) Double IF
If thisCell.value = "C" then
If thisCell.Font.ColorIndex = CI
Kount = Kount +1
End if
End if

B) Boolean
If thisCell.value = "C" And thisCell.Font.ColorIndex = CI
Kount = Kount +1
End if


best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme


"Opal" wrote in message
...
I have a range of cells and I need to count how many
have a blue background and a cell value of "C" and how
many have a green background and a cell value of "C"

I can count all the cells in the range that are blue or
green and I can count all the cells that contain the
value of "C", but how can I count the combined
criteria?

I'm thinking I just need some algebraic help... :-S

Is it possible to combine countif and countcolor?
 

See http://www.xldynamic.com/source/xld.ColourCounter.html for a working
example

HTH

Bob

"Opal" wrote in message
...
I have a range of cells and I need to count how many
have a blue background and a cell value of "C" and how
many have a green background and a cell value of "C"

I can count all the cells in the range that are blue or
green and I can count all the cells that contain the
value of "C", but how can I count the combined
criteria?

I'm thinking I just need some algebraic help... :-S

Is it possible to combine countif and countcolor?
 

Ah...

So:

=SUMPRODUCT((--(colorindex(B6:B34)=35))*(B6:B34="C"))


thank you!!

Is it possible to combine countif and countcolor?
 

You did not tell us that someone has given you a UDF called 'colorindex' -
it is not part of native Excel
best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"Opal" wrote in message
...
Ah...

So:

=SUMPRODUCT((--(colorindex(B6:B34)=35))*(B6:B34="C"))


thank you!!

Is it possible to combine countif and countcolor?
 

I think he is using the ColorIndex UDF at the link I supplied.


---
HTH

Bob Phillips

"Bernard Liengme" wrote in message
...
You did not tell us that someone has given you a UDF called 'colorindex' -
it is not part of native Excel
best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"Opal" wrote in message
...
Ah...

So:

=SUMPRODUCT((--(colorindex(B6:B34)=35))*(B6:B34="C"))


thank you!!