Summing part of filter list with formula or macro

R

rb

New Member
Joined
Sep 8, 2003
Messages
22
I have a "filtered databse" where I want to sum the data in the first few "visible" rows (assume 10 rows). I know the "=subtotal(9,range)" formula, but it doesn't work effectively, as each time I change the filter, the range changes in the formula. Is there a way around this to always select the first 10 visible rows without row numbers being specified?

Below is a macro that I recorded that works porly, because the row information for each selection would always have to be the same. This is a problem since the data will change weekly and thus the row numbers will be different effectively nullifying the usefulness of a macro.

Selection.AutoFilter
Selection.AutoFilter Field:=15, Criteria1:="2003"
Selection.AutoFilter Field:=14, Criteria1:="WEEK 1"
Selection.AutoFilter Field:=13, Criteria1:="MEN'S"
Sheets("Test").Select
Range("D11").Select
Sheets("MARS Data TY").Select
Selection.AutoFilter Field:=13, Criteria1:="WOMENS"
Sheets("Test").Select
ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,'MARS Data TY'!R[4]C:R[45]C[3])"
Range("D12").Select
Sheets("MARS Data TY").Select
Selection.AutoFilter Field:=13, Criteria1:="ACCESS"
Sheets("Test").Select
ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,'MARS Data TY'!R[4]C:R[822]C[3])"
Range("D13").Select
Sheets("MARS Data TY").Select
Selection.AutoFilter Field:=13, Criteria1:="BOYS"
Sheets("Test").Select
ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,'MARS Data TY'!R[4]C:R[291]C[3])"
Range("D14").Select

Also, Are there any great books out there on Excel and/or VBA (I have "Mastering Excel 2000 Premium Edition by Sybex" & "Microsoft Excel 2000 Power Programming with VBA by John Walkenbach"). Both these books are good for initially understanding some beg and intermediate things, but after this they don't help with complex items.

Thanks in advance for any help you provide.

Rick
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Sort by date Sort by votes
Usually when I use filtered lists, I make sure that nothing else is above or below those lists. Then, SUBTOTAL(9,C:C) will always give the sum of the filtered rows in that column.

HTH

Denis
 
Upvote 0
Thank you,

Actually, I don't want to sum the entire column (that won't work in this situation for me). I literally want to sum only the first 30 numbers in a list that is about 200 rows long after I have filtered it down as far as possible.
 
Upvote 0
This can be accomplished with the addition of an extra column (we could do it all in one formula, but it would get rather large.

Given the source data in cols a & b, add another that will provide an incremental count of te filtered items using subtotal(2,range) as shown. The count of the first n filtered cells can then be done with the following:

=SUMPRODUCT((SUBTOTAL(3,OFFSET(A2:A20,ROW(A2:A20)-MIN(ROW(A2:A20)),,1)))*(B2:B20)*(C2:C20<=F1))

which needs to be array entered using control + shift + enter,not just enter, and assumes that n is specified in F1:
Book1
ABCDEF
1IDNumCountHowmany?6
2111Sumis21
3222
4133
5244
6155
7266
8177
9288
10199
1121010
1211111
1321212
1411313
1521414
1611515
1721616
1811717
1921818
Sheet3


...re-create the data as above & stick a filter on it to see the effect. post back if you need more.
 
Upvote 0
Thank you,

This solution was working great until I tried to apply it to 40,000 lines of data...the calc time started getting pretty long.

It will definitly be very useful in other spreadsheets I am using with less data. The formula is definitly beyond what I could have come up with.

Rick
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Locked
  • Solved
select a cell in a filtered row
Replies
3
Views
169
Peter_SSs
Peter_SSs
I
  • Question Question
Filter column on text value if that value exists using VBA
Replies
7
Views
534
Peter_SSs
Peter_SSs
S
  • Question Question
Macro VBA Code to delete filtered rows in a list
Replies
1
Views
93
saboh12617
S
Guzzlr
  • Question Question
Auto Filter is lost when removed
Replies
8
Views
378
Guzzlr
Guzzlr
G
  • Question Question
Sort (Z-A) Macro not working
Replies
1
Views
148
NateSC
NateSC

Forum statistics

Threads
1,225,902
Messages
6,187,729
Members
453,436
Latest member
MEZHH

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top