In Excel, how do I "Countif" a cell is a certain format?
 

If I choose a series of cells and I want to determine how many of those cells
have a fill color of Red, how many have green, etc..., can I do that?

Answer: In Excel, how do I "Countif" a cell is a certain format?
 

Yes, you can use the COUNTIF function in Excel to count cells that meet a certain criteria, including cells with a specific fill color. Here's how:

1. Select the range of cells you want to count.
2. Click on the Home tab in the ribbon at the top of the Excel window.
3. Click on the Conditional Formatting button in the Styles group.
4. Choose Highlight Cells Rules and then More Rules from the dropdown menu.
5. In the New Formatting Rule dialog box, select Use a formula to determine which cells to format.
6. In the Format values where this formula is true field, enter the formula
   Formula:

   ---

   =CELL("color",A1)=1 


   ---

   (without the quotes), where "A1" is the first cell in your selected range. This formula will highlight any cells in the range that have a fill color of red.
7. Click on the Format button and choose a fill color to use for the highlighted cells.
8. Click OK to close the Format Cells dialog box, and then click OK again to close the New Formatting Rule dialog box.
9. Now that you've highlighted the cells with a certain fill color, you can use the COUNTIF function to count them. For example, to count the number of cells with a red fill color, enter the formula
   Formula:

   ---

   =COUNTIF(A1:A10,CELL("color",A1)=1) 


   ---

   (without the quotes), where "A1:A10" is the range of cells you want to count.

Repeat the above steps for each fill color you want to count. Just make sure to change the formula in step 6 to for green, for blue, and so on.

In Excel, how do I "Countif" a cell is a certain format?
 

If the colours are not conditional formats then check out this link...

http://www.xldynamic.com/source/xld.ColourCounter.html

If the colours are based on conditional formats then you can use regular
formulas...
--
HTH...

Jim Thomlinson


"Pheasants21" wrote:

If I choose a series of cells and I want to determine how many of those cells
have a fill color of Red, how many have green, etc..., can I do that?

In Excel, how do I "Countif" a cell is a certain format?
 

Hi,

You can add the following code to your workbook

Function CountFormats(R As Range, E As Range) As Integer
Application.Volatile
Dim cell As Range
Dim Total As Integer
Dim T As Boolean
Set S = E.Cells(1, 1)
Total = 0
For Each cell In R
T = True
With cell
If .Font.ColorIndex < S.Font.ColorIndex Then T = False
If .Interior.ColorIndex < S.Interior.ColorIndex Then T = False
If .Font.Bold < S.Font.Bold Then T = False
If .Font.Italic < S.Font.Italic Then T = False
If .Font.Underline < S.Font.Underline Then T = False
End With
If T = True Then
Total = Total + 1
End If
Next cell
CountFormats = Total
End Function

In the spreadsheet you can enter the function as follows:

=CountFormats(A1:F13,H9)

This function will count the number of cells in the range A1:F13 that have
the same formatting as cell H9. This function check five types of
formatting, you can remove any of those from the code. Font Color, Interior
Color, Bold, Italic, Underline. As written all these formats must match, so
if you only want to deal with fill color it would read

Function CountFormats(R As Range, E As Range) As Integer
Application.Volatile
Dim cell As Range
Dim Total As Integer
Dim T As Boolean
Set S = E.Cells(1, 1)
Total = 0
For Each cell In R
T = True
With cell
If .Interior.ColorIndex < S.Interior.ColorIndex Then T = False
End With
If T = True Then
Total = Total + 1
End If
Next cell
CountFormats = Total
End Function

--

Thanks,
Shane Devenshire


"Pheasants21" wrote:

If I choose a series of cells and I want to determine how many of those cells
have a fill color of Red, how many have green, etc..., can I do that?