Subtotal question

[Applebus]

Hello everyone, I am a bit confused with Subtotal function in my file. Please download my file to see the problem.

When I sort the person name "Name 1" everything works including column N(Subtotal value). but when i sort the person name " Name 2", the value in column N is not correct. N118 should be 30 but it shows 0. As a result, the value after N118 is not correct.

Please help me figure it out how to solve this one.
Thank you.

Edit: Update file

[AliGW]

What are you expecting the answer to be when you filter?

[Applebus]

I expect the the value in column N not to be 0 but 30 as in this file.

As I understand, subtotal(9,ref) is used to sum the value which is not hidden when data is sorted.
In this file, when I sort "Name2", subtotal in N117 should be 0 which is correct and N118 should be 30 not 0 as it sums N117 too which is not hidden.
so I am confused why the value of N118 is 0.

[Sadath31]

hi

=AGGREGATE(9,5,$J$3:J117) instead of subtotal

[boss1982]

Hi,

when you filter name 1, the subtotal in column J = 30
when you filter name 1, the subtotal in column J = 990

your formula in column N is =SUBTOTAL(9,ref), accordingly, the formula in
cell N118: =SUBTOTAL(9,$J$3:J117) = 30
cell N119: =SUBTOTAL(9,$J$3:J118) = 60

means the value in column N always increased by 30, 30 to 60 to 90 to 120 to 150..etc., but you have mentioned in your file that the value should be always 30 which is wrong

if the above is correct, please check the attached file, it is solved by adding new column after column J, namely column K, and column N which automatically will be column O will take the ref from column K instead of J.

please confirm...
thanks.

Edit: the formula provided by Sadath31 will give you the same result also...it is correct but i noticed it after i post mine.

[Applebus]

Thank you very much, boss1982 and Sadath31.
it really helps me solve the problem. and yes the value "30" is wrong.
I also googled how to use aggregate as I've never used it before.
Also some tips for using subtotal https://stackoverflow.com/questions/...als-shows-zero

[boss1982]

glad to know that it is solved now, please Add Reputation to the persons who helped you and mark the threads as "solved"

[Applebus]

Done!! Again, Thank you very much