Announcement

Collapse
No announcement yet.

Subtotal Filtered Column By Criteria

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Subtotal Filtered Column By Criteria

    Hello,

    In my attached sheet there are two textboxes. In textbox1, I would like to see count of unique visible numbers in column D, and in textbox2, i would like to see count of visible text "Yes" from column C. I will be applying autofilter in the table so I want to get a sum in the textbox when the autofilter is triggered. Can someone help? Thanks.
    Attached Files
    Last edited by l4ya; April 18th, 2008, 06:24.

  • #2
    Re: Subtotal In A Textbox

    Have a look at the subtotal formula (it can average, sum, etc all visible cells), add it to a cell and link the cell to your textbox.

    eg:


    Try this formula. Array formulas will have curly brackets {} emcompassing the ENTIRE formula. You do not add these, you Enter the formula with Ctrl+Shift+Enter and Excel will add them (details: Array formulas). Many more Excel Formulas

    =SUBTOTAL(9,D:D)
    Reafidy

    Forum Rules | Anatomy Of A Good Thread Title | What Have You Tried So Far? | Are You A Cross Poster?

    Comment


    • #3
      Re: Subtotal In A Textbox

      Try :
      Code:
          Dim c As Range, Coll As Collection, Ctr As Long
          On Error Resume Next
          For Each c In _
          Intersect(Range("_filterdatabase"), [C:C]).Offset(1). _
          Resize(Range("_filterdatabase").Rows.Count - 1).SpecialCells(xlCellTypeVisible)
              If c = "Yes" Then
                  Ctr = Ctr + 1
              End If
          Next c
          ActiveSheet.TextBox1.Text = Ctr
          Ctr = 0
          Set Coll = New Collection
          For Each c In _
          Intersect(Range("_filterdatabase"), [D:D]).Offset(1).Resize(Range("_filterdatabase"). _
          Rows.Count - 1).SpecialCells(xlCellTypeVisible)
              Coll.Add CStr(c.Value), CStr(c.Value)
          Next c
          On Error GoTo 0
          For Each Item In Coll
              Ctr = Ctr + 1
          Next Item
          ActiveSheet.TextBox2.Text = Ctr
      HTH
      Daniel
      Regards.
      Daniel

      Comment


      • #4
        Re: Subtotal In A Textbox

        Hmm I didnt read the second part properly and it needed a more advanced formula but still no code needed, see attached.


        Try this formula. Array formulas will have curly brackets {} emcompassing the ENTIRE formula. You do not add these, you Enter the formula with Ctrl+Shift+Enter and Excel will add them (details: Array formulas). Many more Excel Formulas

        =SUMPRODUCT(SUBTOTAL(3,OFFSET(C:C,ROW(C1:C65535)-MIN(ROW(C1:C65535)),,1)), --(C1:C65535="YES"))


        I bet DDLL can simplify it even more.
        Attached Files
        Last edited by Reafidy; April 18th, 2008, 09:53.
        Reafidy

        Forum Rules | Anatomy Of A Good Thread Title | What Have You Tried So Far? | Are You A Cross Poster?

        Comment


        • #5
          Re: Subtotal In A Textbox

          Hi,

          For first one.

          =SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(D3:D34,ROW(D3:D34)-ROW(D3),,1)),MATCH(D3:D34,D3:D34,0)),ROW(D3:D34)-ROW(D3)+1),1))

          Array entered. To Enter the array formula hold down Ctrl and Shift while pushing Enter.

          HTH
          Kris

          ExcelFox

          Comment


          • #6
            Re: Subtotal Filtered Column By Criteria

            Hello,

            Thank you very much for the reply. Actually I am not looking for CSE formula because what I know about CSE formula is that you always have to use CSE to see the result. I am looking for a code that will automatically show the count of unique visible numbers from the column D in textbox1, and count of the word "Yes" from visible column in textbox2. My users will be using autofilter and I would appreciate if someone can give me a code that will show the result. Actually I don't know VB; I will try Daniel's code and see how it fits in. Thanks again.

            Comment


            • #7
              Re: Subtotal Filtered Column By Criteria

              If you want futher help please offer some feedback on the file I supplied you, I dont understand what is wrong with it.

              I really dont think you need code for this.
              Reafidy

              Forum Rules | Anatomy Of A Good Thread Title | What Have You Tried So Far? | Are You A Cross Poster?

              Comment


              • #8
                Re: Subtotal Filtered Column By Criteria

                Hello Daniel,

                Thank you very very much very supplying the code to me. It solves my problem. Your help and all the responses are very much appreciated.

                Comment

                Working...
                X