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
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.
-
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
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.
- Press Alt+F11 to start the Visual Basic editor.
- Insert > Module
- Insert above code
- 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.