# subtotal sum for first X visible rows

#### ilscfn

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

### Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

#### pgc01

##### MrExcel MVP
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)))

#### ilscfn

##### New Member
it worked like a charm!! Thanks so much for your help PGC!!

#### pgc01

##### MrExcel MVP
You're welcome. Thanks for the feedback.

Replies
3
Views
127
Replies
0
Views
45
Replies
1
Views
127
Replies
7
Views
414
Replies
7
Views
203

1,127,998
Messages
5,628,046
Members
416,289
Latest member
Jbelisari

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

### Which adblocker are you using?

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

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