=SUBTOTAL behaving differently

[seriousnick]

Hi All and a Happy New Year

When using a filtered range, previously I was able to use AutoSum to sum the visible cells.
The formula applied to the resulting cell was always =SUBTOTAL(9, cell1, cell2, cell3, etc).
This meant that when I "unfiltered", the value remained the same (which is what I required).

Today, I am now only getting a range in the resulting formula - e.g. =SUBTOTAL(9, G5:G241).
In order to achieve my desired result, I can pick each total using CTRL but this will take an age.

Is there some switch I may have clicked perhaps? I am using the exact same excel on the same PC and only a week or 2 later.
Even if I go to a worksheet that had previously worked as above, when I delete the cell with the correct formula and try SUBTOTAL again - I get the range formula as opposed the individual cell formula.

I have tried all sorts of things like removing and reapplying filter, copying range to a new sheet, trying the total at the top of the range etc.

Also there appears no help from a google search.

Any help much appreciated.

All the very best

Nick

 

[Ron Coderre]

To my knowledge, Excel never automatically built: =SUBTOTAL(9, cell1, cell2, cell3, etc)
and always automatically referenced a range. You would need to build that kind of formula cell by cell.

 

[RoryA]

I don't think I've ever seen autosum do what you are describing (since the whole point of the SUBTOTAL function is to ignore filtered out cells).

 

[seriousnick]

Thanks for prompt responses.
Hmm, very odd, a few days back, I would just click AutoSum and it would select the full range of the filtered results to the top row.
I have attached a screenshot of a recently created sheet.
I suppose if you guys don't know then nobody will!

 

[Ron Coderre]

Assuming you somehow got that formula to build itself.... The SUBTOTAL function can only reference a maximum of 254 individual ranges, potentially limiting it usefulness.

 

[seriousnick]

Not to go on too much about this, but please see shared screen video of the "auto subbing" in action.
For some reason it is now working on the original sheet created a few days ago, but still not on the other.

2020-01-02 16-36-53.mkv

MKV File

1drv.ms

 

[seriousnick]

For those who may be interested, the resolution to this issue is as follows:

I was trying to "sum" a list of "subtotals".
If the last subtotal is only totalling 1 row (and therefore will have the same value) the auto subbing does not work.
If I remove that single subtotal, (and the row containing the value it was totalling) then it works just fine.
Please see short video.
Thanks.

2020-01-02 17-04-17.mkv

MKV File

1drv.ms