3

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?

2 Answers 2

2

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

enter image description here

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

enter image description here

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

enter image description here

1
  • I would have preferred an answer that said, "of course you can! here's how to do it!" but, alas, you're probably right. There's no way to do what I want without using VB. Thanks. May 3, 2012 at 18:24
0

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.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .