Forum Discussion
Brenainn
Feb 01, 2023Copper Contributor
COUNTIF with formatting
Hi! We have a spreadsheet that is quite a bit of a disaster. It had to be built by someone who didn't know how to build a spreadsheet. There are 3 main titles that exist in cells A1-D1, E1-H1, an...
HansVogelaar
Feb 02, 2023MVP
Here is a custom VBA function.
Function CountName(MyRange As Range, MyName As String) As Long
Dim MyCell As Range
Dim MyLines() As String
Dim MyLine As String
Dim MyStart As Long
Dim i As Long
Dim f As Boolean
For Each MyCell In MyRange
MyLines = Split(MyCell.Value, vbLf)
If UBound(MyLines) >= 4 Then
MyLine = MyLines(4)
If MyLine = MyName Then
CountName = CountName + 1
Else
MyStart = 0
For i = 0 To 3
MyStart = MyStart + Len(MyLines(i)) + 1
Next i
MyStart = MyStart + InStr(1, MyLine, MyName, vbTextCompare)
f = True
For i = 0 To Len(MyName) - 1
If Not MyCell.Characters(MyStart + i, 1).Font.Bold Then
f = False
Exit For
End If
Next i
If f Then
CountName = CountName + 1
End If
End If
End If
Next MyCell
End Function
Use like this:
=CountName(A3:M3, "Gold")
Save the workbook as a macro-enabled workbook (*.xlsm).
Make sure that you allow macros when you open it.