Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

# Thread: Subtotal Filtered Column By Criteria

1. I agreed to these rules
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.
Last edited by l4ya; April 18th, 2008 at 06:24.

Excel Video Tutorials / Excel Dashboards Reports

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)

3. Established Member
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)
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. ## 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.
Last edited by Reafidy; April 18th, 2008 at 09:53.

5. Super Moderator
Join Date
18th November 2004
Location
God's Own Country
Posts
4,648

## 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. I agreed to these rules
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. ## 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. I agreed to these rules
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

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

#### 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