Results 1 to 2 of 2

Sumproduct subtotal filter count only bold cells

Threaded View

  1. #1
    Registered User
    Join Date
    03-05-2009
    Location
    Vail, Colorado
    MS-Off Ver
    Excel 2010
    Posts
    83

    Post 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]))"
    Last edited by RxMiller; 07-20-2011 at 01:30 PM. Reason: gif did not load

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1