Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Results 1 to 8 of 8

Thread: Subtotal Filtered Column By Criteria

  1. #1
    Join Date
    18th April 2008
    Posts
    18

    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. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros. Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.
    Last edited by l4ya; April 18th, 2008 at 06:24.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    8th December 2006
    Location
    New Zealand
    Posts
    4,284

    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)

  3. #3
    Join Date
    19th March 2008
    Posts
    449

    Re: Subtotal In A Textbox

    Try :
    VB:
    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

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    8th December 2006
    Location
    New Zealand
    Posts
    4,284

    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. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros. Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.
    Last edited by Reafidy; April 18th, 2008 at 09:53.

  5. #5
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,678

    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

  6. #6
    Join Date
    18th April 2008
    Posts
    18

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    8th December 2006
    Location
    New Zealand
    Posts
    4,284

    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.

  8. #8
    Join Date
    18th April 2008
    Posts
    18

    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.

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Subtotal Function With Criteria
    By jbkuppe in forum Excel General
    Replies: 5
    Last Post: June 21st, 2008, 09:54
  2. Subtotal Based On Criteria
    By Dauntless1 in forum Excel General
    Replies: 4
    Last Post: September 25th, 2007, 09:44
  3. Subtotal Based On 2 Criteria
    By ray_uk in forum Excel General
    Replies: 1
    Last Post: July 31st, 2007, 05:24
  4. Subtotal Sum With Criteria
    By Tom Cruise in forum Excel General
    Replies: 7
    Last Post: October 3rd, 2006, 10:38
  5. Subtotal Formula With Criteria
    By Kim1978 in forum Excel General
    Replies: 4
    Last Post: April 16th, 2005, 17:02

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
  •  
porno