Countif and countcolor

[smilinglilies]

Hi

I am currently trying to create a formula to count the number of cells of a certain colour that is a certain criteria.

I'm looking for something like a countcolorif function but can't work it out and I'm getting frustrated that I can't work this out!

Basic version below of spreadsheet. (Cells are coloured instead of typed in)

How do I write a formula that will count the number of orange cells in the rows that have a 2 in the first column?

[TABLE="width: 500"]
<tbody>[TR]
[TD]No Bedrooms
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]orange[/TD]
[TD]grey[/TD]
[TD]blue[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]blue[/TD]
[TD]orange[/TD]
[TD]grey[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]blue[/TD]
[TD]grey[/TD]
[TD]orange[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]blue[/TD]
[TD]grey[/TD]
[TD]orange[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]grey[/TD]
[TD]blue[/TD]
[TD]grey[/TD]
[/TR]
</tbody>[/TABLE]

I know I can do this by countcolour () + countcolour() but I was wondering if there is a different and not as long winded way of doing it?

Any help and advice is greatly appreciated.

 

[Ramballah]

Ah right - so it looks like it does need to be the long winded version, I just thought that there might be a quicker way to put it all together.

Thank you for your help

Np, u can just autofill it though if u make the references absolute

 

[Fluff]

How about

Function CountCcolor(range_data As Range, crit1 As Range, Crit2 As Long) As Long
   Dim datax As Range, datac As Range
   Dim xcolor As Long
   xcolor = crit1.Interior.ColorIndex
   For Each datax In range_data.Rows
      If datax.Cells(1, 1).Value = Crit2 Then
         For Each datac In datax.Cells
            If datac.Interior.ColorIndex = xcolor Then
               CountCcolor = CountCcolor + 1
            End If
         Next datac
      End If
   Next datax
End Function

Excel 2013/2016
	A	B	C	D	E
1
2	2	orange	grey	blue	2
3	2	blue	orange	grey
4	3	blue	grey	orange
5	3	blue	grey	orange
6	2	grey	blue	grey
Sheet12

Cell Formulas
Range	Formula
E2	=CountCcolor(A2:D6,B2,2)

 

[steve the fish]

My take on this if ive understood. Beware that formatting a cell isnt going to trigger a recalc of the cell!

Function CountColor(Num_Range As Range, Color_Range As Range, criteria As Range) As Long

Dim myColor As String, myVal As Long, xl As Long, x2 As Long, y1 As Long, y2 As Long
Dim c As Range, a As Long

If Num_Range.Rows.Count <> Color_Range.Rows.Count Or Num_Range.Columns.Count <> Color_Range.Columns.Count Or criteria.Cells.Count > 1 Then
    CountColor = CVErr(xlErrNA)
    Exit Function
Else
    myColor = criteria.Interior.ColorIndex
    myVal = criteria.Value
    x1 = Num_Range.Cells(1).Row: x2 = Color_Range.Cells(1).Row
    y1 = Num_Range.Cells(1).Column: y2 = Color_Range.Cells(1).Column
    
    For Each c In Num_Range
        If c.Value = myVal Then
            If c.Offset(x2 - x1, y2 - y1).Interior.ColorIndex = myColor Then
                CountColor = CountColor + 1
            End If
        End If
    Next
End If

End Function