Thread: Sumproduct subtotal filter count only bold cells

Need custom Formula to count only bold cells in a filtered column - using a sumproduct with subtotal

My report uses TSQL to bring data from a database and then uses VBA to create a Excel report. The report has an auto filter across the columns.
The records returned are bolded for the first instance of a name, and the second instance is a light gray.
The Excel cell B4 has a formula that counts the visible rows for the text matching in A4.

Now, I need to only count the bold cells.
Shown is the working formula in the Cell. The working code that puts the formula in the cell, and a small screen shot.

My guess is that some kind of conditional format need to be embedded in the subtotal section. Any suggestions would be appreciated.

Excel cell B4 contains

=SUMPRODUCT(SUBTOTAL(3, OFFSET(C6:C591, ROW(C6:C591)-ROW(C6),0,1)),--(C6:C591=A4))

VBA code counts recordset (intMaxRecordCount) and generates dynamic formula

ObjXL.Range("B4").Select
                  ObjXL.Range("B4").Select   ' offset 2
          ObjXL.ActiveCell.FormulaR1C1 = _
              "=SUMPRODUCT(SUBTOTAL(3, OFFSET(R[2]C[1]:R[" & intMaxRecordCount & "]C[1], ROW(R[2]C[1]:R[" & intMaxRecordCount & "]C[1])-ROW(R[2]C[1]),0,1)),--(R[2]C[1]:R[" & intMaxRecordCount & "]C[1]=RC[-1]))"

The Gif mentioned above is here

Got the solution. Will modify my OBXL string and add two more line of code before it.
It won't require a UDF (user defined function) with all the macro workbook mess.

Was watching the series Starhunter 2300 "Just Politics" last night on NetFlicks for the first time (catching up with series I completely missed).
The Wave Overlay programming concept used as the centerpeice of the episode gave me the idea for the solution.
Can use the formula posted above with some very slight modifications.

Couldn't find any solutions other than a UDF anywhere. And, UDF won't necessarly update in a dynamic way.
This solution will be dynamic, and only count the bold rows that are visible, and won't show an error if zero rows.