Hello, Within Excel, I have a matrix of programs horizontally and accounts verti

Question

Hello,

Within Excel, I have a matrix of programs horizontally and accounts vertically and I have evaluated the performance of each intersection of the maxtrix with red (has concerning issues), yellow (has some issues), and green (no issues). At the bottom of my matrix, I'd like to use a COUNTIF formula to count the number of cells that are red, yellow, and green. What's the best way to COUNTIF on cell color? If needed, I can create a separate matrix that identifies the cell color by number (red,green,blue combination) and then do a lookup on that matrix.

Thanks

Explanation / Answer

Hi,

Excel has no native way of doing this so we must create a UDF to do it. ALT+F11 to open vb editor, right click 'ThisWorkbook' and insert module and paste the code below in. Close VB editor.

Back on the worksheet call with

=Countcolour($A$1:$J$14,K1)

Where:-

A1:J1 is the range you want to count
K1 is a cell with the fill colour you want to count.

Function Countcolour(rng As Range, colour As Range) As Long
Dim c as Range
Application.Volatile
For Each c In rng
    If c.Interior.ColorIndex = colour.Interior.ColorIndex Then
        Countcolour = Countcolour + 1
    End If
Next
End Function

Related Questions

Navigate

• Browse (All)
• Browse H
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.