subtotal sum for first X visible rows

[ilscfn]

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

 

[pgc01]

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]

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

 

[pgc01]

You're welcome. Thanks for the feedback.