[fyogi1]

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]

Here's 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 doesn't 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" every time 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:

http://www.microsoft.com/exceldev/articles/xlevnts.htm

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?

[cltan]

First open the Workbook in which you wish to count or sum cells by a fill color. Now go into the Visual Basic Editor via Tools>Macro>Visual Basic Editor (Alt+F11) and then, from within the Visual Basic Editor go to Insert>Module to insert a standard module. Now, in this module, enter the code as shown below;
Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean)

Dim rCell As Range

Dim lCol As Long

Dim vResult



''''''''''''''''''''''''''''''''''''''

'Written by Ozgrid Business Applications

'www.ozgrid.com



'Sums or counts cells based on a specified fill color.

'''''''''''''''''''''''''''''''''''''''



lCol = rColor.Interior.ColorIndex



If SUM = True Then

For Each rCell In rRange

If rCell.Interior.ColorIndex = lCol Then

vResult = WorksheetFunction.SUM(rCell,vResult)

End If

Next rCell

Else

For Each rCell In rRange

If rCell.Interior.ColorIndex = lCol Then

vResult = 1 + vResult

End If

Next rCell

End If



ColorFunction = vResult

End Function
________________________________________
You can now use the custom function (ColorFunction) like;
=ColorFunction($C$1,$A$1:$A$12,TRUE) to SUM the values in range of cells $A$1:$A$12 that have the same fill color as cell $C$1. The reason it will SUM in this example is because we have used TRUE as the last argument for the custom function.
To COUNT these cells that have the same fill color as cell $C$1 you could use:
=ColorFunction($C$1,$A$1:$A$12,FALSE) or =ColorFunction($C$1,$A$1:$A$12) by omitting the last argument our function will automatically default to using FALSE.
Be aware that the changing of a cells fill color will not cause the Custom Function to recalculate, even if you press F9 (Recalculates the whole Workbook). You will need to either, select the cell and re-enter the formula, or go to Edit>Replace and replace = with =, or use Ctrl+Alt+F9
Try also to avoid the use of Application.Volatile as it will not help in this case and only slow down Excel's calculation time.

[muscleflex]

Thanks to retsoksirhc. His solution worked for me and I have been searching online since last week!
Thanks

[bgloyalty]

This works great for counting the number in the cell, which is what it says it does. But I want to count the number of cells that are that color, regardless of what value is inside (the values will be letters). I've tried modifying the module but haven't been successful. Any help on how to get excel to do this?

Thank you!
James

First open the Workbook in which you wish to count or sum cells by a fill color. Now go into the Visual Basic Editor via Tools>Macro>Visual Basic Editor (Alt+F11) and then, from within the Visual Basic Editor go to Insert>Module to insert a standard module. Now, in this module, enter the code as shown below;
Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean)

Dim rCell As Range

Dim lCol As Long

Dim vResult



''''''''''''''''''''''''''''''''''''''

'Written by Ozgrid Business Applications

'www.ozgrid.com



'Sums or counts cells based on a specified fill color.

'''''''''''''''''''''''''''''''''''''''



lCol = rColor.Interior.ColorIndex



If SUM = True Then

For Each rCell In rRange

If rCell.Interior.ColorIndex = lCol Then

vResult = WorksheetFunction.SUM(rCell,vResult)

End If

Next rCell

Else

For Each rCell In rRange

If rCell.Interior.ColorIndex = lCol Then

vResult = 1 + vResult

End If

Next rCell

End If



ColorFunction = vResult

End Function
________________________________________
You can now use the custom function (ColorFunction) like;
=ColorFunction($C$1,$A$1:$A$12,TRUE) to SUM the values in range of cells $A$1:$A$12 that have the same fill color as cell $C$1. The reason it will SUM in this example is because we have used TRUE as the last argument for the custom function.
To COUNT these cells that have the same fill color as cell $C$1 you could use:
=ColorFunction($C$1,$A$1:$A$12,FALSE) or =ColorFunction($C$1,$A$1:$A$12) by omitting the last argument our function will automatically default to using FALSE.
Be aware that the changing of a cells fill color will not cause the Custom Function to recalculate, even if you press F9 (Recalculates the whole Workbook). You will need to either, select the cell and re-enter the formula, or go to Edit>Replace and replace = with =, or use Ctrl+Alt+F9
Try also to avoid the use of Application.Volatile as it will not help in this case and only slow down Excel's calculation time.

[JBeaucaire]

The ColorFunction has 3 parameters, if you leave out the 3rd parameter or set it to FALSE, then it will give you a count, just like you want.

[anshul89]

Thanks cltan... Your solution worked for me. Cheers!