I am trying to count a large group of cells that are colored depending on the data. For example: I want to count the number of red cells which are faults. I tried the countif but not sure how to set the criteria.

Thanks for any help.

retsoksirhc

Heres the basic syntax

=countif(cells, condition)

so you would take whatever you are calculating as a fault and put that under the conditions part (for example, <5). The cells are whichever ones you are trying to count with (ie. a1:e5). Assign this to a cell being sure to have an = at the beginnging to show that it is a calculation, and that cell will show the number of cells in a1:e5 that are <5.

fyogi1

Thanks for your response. I got that far but I want to set the condition as the color, not a number or text in the cell. I have a workbook with multiple cells filled in with different colors. I want to count certain color of cells.

Dreamboat

Hi, fyogi1.

This requires a user-defined function (UDF). Check out Chip Pearson's site:
http://www.cpearson.com/excel/colors.htm

I'm sure it's a bit late, but you'll have it for next time.

leif5233

Create this function:

Function CountCol(SumRange As Range, intColor As Integer) As Integer
Dim i As Integer
Dim Cell As Range

Set SumRange = SumRange.SpecialCells(xlCellTypeAllFormatCondition s)

For Each Cell In SumRange
If Cell.Interior.ColorIndex = intColor Then
i = i + 1
End If

Next Cell

CountCol = i

End Function

Then in the cell where you would like the total, put the following formula:

=CountCol(a1:a100,6)

That formula will show you the total count of all cells with colorindex of 6 (Yellow) in range a1:a100. You can fix it up to use color names if you like, but this works fine for me. There is a listing of basic colorindexes in Excel's help.

Hope this helps,
Leif

DANZ

is there anyway to recalculate the total if a colour is changed? as it seems excel doesnt class a fill colour as a value.

leif5233

There are cleaner (and more complicated) ways to auto-calc, but the simplest I can offer is this. Add:

Application.Volatile (True)

above the "Dim" statements in the code I listed before.

Then, just hit "F9" everytime you want the cells to recalc.

If you want it to recalc automatically, you'll have to create a class module and override one of the application object's standard events. Here's a link for you to check out if interested:

Leif

h_arveys

I have the same issue. I want to count the number of entries that are "RED". I know there is an excel formula that does it because I have done it before. Unfortunately I have deleted the work book that had the formula so I am searching for it again.

example: =countif(a1:a15, fontcolor="red") or something like that...I have tried all the variations I can think of so I must not have it right.

Any ideas?