excel countif filtered by style

Question

I have an Excel spreadsheet and I'm trying to count rows in a column based on a style applied to some cells in the column. Is there a simple way to do this?

Answer 1

No, not really. You could use Visual Basic to access cell formatting properties but most inbuilt functions that you would type into a cell focus on the cell contents, not the formatting.

If your styles have different shading colours then you can use the following method.

Step 1: Convert your range to a list and then adding a total row showing COUNT

Step 2: Apply a colour filter (should work on Excel 2007 and later):

Done: The COUNT total will show the filtered number of rows.

Answer 2

You could use VBA for that:

Function CountStyle(CellRange)
   Dim Item As Range, Total As Long
   For Each Item In CellRange
      ' Check to see if the cell is formatted as Style = "Neutral"
      If Item.Style = "Neutral" Then
         Total = Total + 1
      End If
   Next Item
   CountStyle = Total
End Function

Taken from here.

1. Press Alt+F11 to start the Visual Basic editor.
2. Insert > Module
3. Insert above code
4. Go to Excel and choose the cell, where the result should be in. Write e.g. =CountStyle (B4:B23)

Now you have count all cells with the style Neutral. I've created three functions for neutral, good, bad. This looks like:

Function CountStyleGood(CellRange)
   Dim Item As Range, Total As Long
   For Each Item In CellRange
      ' Check to see if the cell is formatted as Style = "Good"
      If Item.Style = "Good" Then
         Total = Total + 1
      End If
   Next Item
   CountStyleGood = Total
End Function

Wit =CountStyleGood(B4:B23) you get the result. As name of the style I've used the name displayed in the ribbon.