subtotal sum for first X visible rows

ilscfn

New Member
Joined
Jun 28, 2013
Messages
2
Hi everyone,

I have a sheet with a couple thousand rows of data. I need to calculate the subtotal sum for the first 50 rows of auto-filtered data. I can't seem to figure out the formula that I would need to have to do this automatically. If for example my filtered data the 50th visible row is row 1300, currently i can write a formula "SUBTOTAL(9,Q1:Q1300) but if the filtering changes I would have to go and change the formula to replace Q1300 with QX, where X is the new 50th visible row number. I know there must be at least one way to do it automatically but it seems to be beyond my current skill level. Does anybody know how to do it? Apologies for my ignorance and thanks in advance for your help!

ils
 

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
Hi
Welcome to the board

An easy way is to use an auxilliary column (that you can hide) to get the order of the visible row.

For ex., you have a table in A1:Q10000, with no empty cells in column A. Using column Z as auxilliary, write in Z2:

=SUBTOTAL(3,$A$2:A2)

Copy to Z2:Z10000

If you test it you'll see that you always get the order of the row, considering only visible rows, when you filter the table.
You can use that order to get the sum of n visible rows.

For ex., to get the sum of the first 10 visible rows:

=SUBTOTAL(9,Q2:INDEX(Q:Q,MATCH(10,Z:Z,0)))
 

Watch MrExcel Video

Forum statistics

Threads
1,127,759
Messages
5,626,695
Members
416,200
Latest member
Pulsar3000

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
Top