Forum Discussion

Brenainn's avatar
Brenainn
Copper Contributor
Feb 01, 2023

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, and I1-M1. The 1st main title has 4 subtitles, as does the 2nd main title. The 3rd main title has 5 subtitles. Each column represents a different stage in the process. Each cell contains 6 labels and their corresponding information: Acct #, Business, Notes, Address, Name, and Request. Below is an example of what the cells look like:

 

Acct #: 12345
Business: ABC Company
Notes: Generic note goes here.
Address: 123 N East Street
Name: Gold/Sterling/Silver
Request: Employees start tomorrow.

 

We can't use a COUNTIF formula because there are some crazy rules attached to this program. Please see below:

 

  • Count if name appears by itself and matches the search criteria
  • Count if name appears in bold and matches the search criteria if listed with other names.
  • Do not count if name appears in group and is not in bold.

 

Here is an example of names:

 

  • Gold
  • Gold
  • Gold
  • Gold
  • Gold
  • Gold/Platinum
  • Gold/Silver

In the above example, Gold would be counted 6 times--5 times for 1 time for when Gold is bold.

 

Does anyone have any idea how we can even go about making this better for our employees? I am working on converting the spreadsheet over to a new format, but I would appreciate any assistance we can get for this particular situation. Thank you in advance! Have a fantastic day!

 

Bray

excel
Formulas and Functions
Macros and VBA
  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Feb 02, 2023

    Brenainn 

    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.

Resources