COUNT IF

[mahadeshwar]

Hi all,

I have a problem,

I want to use the COUNTIF OR SUM IF FUNCTION TO COUNT THE NUMBER OF CELLS DEPENDING ON THEIR COLOUR

FOR EG IN SHEET 1 THERE ARE 6 CELLS RED IN COLUMN F
7 CELLS BLUE IN COLUMN F
8 CELLS GREEN IN COLUMN F

I NEED TO PRINT IN SHEET TO HOW MANY CELLLS IN TOTAL

ARE RED , BLUE AND GREEN

HOW DO I DO THAT BIGH SHOUT OUT TO TONY IN THE FORUM FOR ALL HIS HELP TOO

[TonyJollans]

Sorry, you can't do this in a formula - you can only check the contents of cells, not the properties. You'll need VBA, or some other way of identifying the cells.

[mahadeshwar]

Oh dam.

does anybody know how i would do that in VB i am not good at vb

many thanks

[johnske]

Something like this...
[VBA]Option Explicit
'
Sub CountColours()
'
Dim Cell As Range, RedCount As Long, BluCount As Long, GreenCount As Long
'
For Each Cell In ActiveSheet.UsedRange
If Left(Cell.Address(0, 0), 1) = "F" Then
Select Case Cell.Interior.ColorIndex
Case 3
RedCount = RedCount + 1
Case 5
BluCount = BluCount + 1
Case 50 '<Sea green
GreenCount = GreenCount + 1
End Select
End If
Next
MsgBox "Red " & RedCount & vbNewLine & _
"Blue " & BluCount & vbNewLine & _
"Green " & GreenCount
End Sub[/VBA]

[TonyJollans]

A simple function might be like this[vba]Function CCC(R as range) as long
for each C in R
if c.interior.Colorindex <> xlcolorindexnone then CCC = CCC + 1
next
End function[/vba]
Then in a cell you could put =CCC(A1:A20) for example

I'm sure an Excel expert could improve on it

[TonyJollans]

I'm sure an Excel expert could improve on it

I see one already has

[johnske]

I see you're new to VBA, in the code I gave above, a single "Select Case" can cover many instances. For example, there are many 'greens', so you could go through the colour pallette and get all the greens and put them in the Green case statement e.g.
[vba]Case 4, 10, 35, 43, 50, 51
GreenCount = GreenCount + 1[/vba]you can then do the same with red and blue. You can also add cases for yellow - or whatever...

HTH