Is it possible to combine countif and countcolor?

[Opal]

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

 

[Bernard Liengme]

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

 

[Bob Phillips]

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

HTH

Bob

 

[Opal]

Ah...

So:

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


thank you!!

 

[Bernard Liengme]

You did not tell us that someone has given you a UDF called 'colorindex' -
it is not part of native Excel
best wishes

 

[Bob Phillips]

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