Please tell me what I am doing incorrectly-Excel SUBTOTAL Function 9 and 109 return the same Results

Question

A Spreadsheet, created using macOS Numbers, was opened in Excel because it offers a wide range Of Functions, and the ability to Hide Cell Content seemed an ideal choice for the project in hand.

So I have commenced a Trial of 365.

Applying the following to this Spreadsheet failed to function as expected and so I set up Test01 in Excel.

In Test01 selections are made from a Column of Products, in this case, identified as 10 Letters.

In the second Column each of these has been given a Value, in this case, they are all 10.00.

In Format the Value Column has been set to Number.

In Sheet1 Custom has been used the Hide the Values of 3 Letter V Products.

In the Total Row =SUBTOTAL(9, B2:B11) returns 100 as the Result.

Because on the Working Sheets some Values are required to be Hidden and the Rows contains them are Copied & Pasted to a Second Working Sheet, Test01:Sheet2 was created similarly.

In Sheet2 the Total Row =SUBTOTAL(109, B2:B11) returns 100 as the Result.

https://1drv.ms/x/s!Au2bj9QvcxAObq50EYUboaxsJz4?e=XH3hC5

Thanks for any help you can offer.

gelphyn · Answer

Gelphyn&nbsp;The following adds to information that may be useful inn determining a solution to the above problem.The lack of responses to the Post prompted me to examine some related matters. Observing that Directions to access features, at deeper levels in Menus, are not up to date it occurred to me that other higher level aspects may also be suffering similarly.Prior to the 365 Installation ti was seen in the Computer Requirements that the Processor should be 'Intel', however the desire to try Excel encouraged the installation disregarding the processor being used.The installation has been completed on a MacBook [2020] Pro M1 with 1TB SSD - 16 Mb RAM., working space is conserved courtesy of 3 External SSDs holding Photos, Data and Time Machine.Given the massive Apple swing toward their own Processors I am hopeful that Microsoft would have, or be in the process, of taking steps toward smooth functionality between 365 and the M range of Processors.Any information will be greatly appreciated. Thank you.

gelphyn · Answer

Needing to progress this matter to, if it becomes necessary, cancel my 365 Subscription before the Trial Month expires it has been reported via the In App Feedback system.

A Link to this Thread has been included in my Feedback and not wishing to waste anyones time include details of the Software involved.

My MacBook Pro 2020 M1 is running macOS Monterey Version 12.1

Excel is Microsoft® Excel for Mac Version 16.56 (21121100) Licence: Microsoft 365 Subscription

Hopefully I have now included all the information that may be needed. Please feel free to inform if there is anything else required. Thank you.

steve_k_excel · Answer

Gelphyn&nbsp;From looking at the workbook, there are values in the cells for the "V" rows, but the format causes the value to not be visible. The Excel SUBTOTAL function does not consider these to be hidden, since the rows are not hidden. If you use a filter to filter out the "V" rows, they will be hidden and the subtotal result should be as expected. You could also hide the rows by right-clicking on the row number and choosing "Hide".&nbsp;

gelphyn · Answer

Steve_K_Excel&nbsp;Thank you for your response.Understanding the purpose of 2 SUBTOTAL Functions, namely '9' and '109', seems to be my difficulty.I accept that what you say is true, and aware that Cell Content is Retained when Hidden, but additionally my understanding is based the following:&nbsp;From Excel &gt; Help &gt; SUBTOTAL [NB. Help Information has been Italicised] :The SUBTOTAL function syntax has the following arguments:Function_num&nbsp;&nbsp;&nbsp;&nbsp; Required. The number 1-11 or 101-111 that specifies the function to use for the subtotal. 1-11 includes manually-hidden rows, while 101-111 excludes them; filtered-out cells are always excluded.I am unsure about "filtered-out cells are always excluded." and what actions are require from the User and therefore any omissions or errors that may have been made or involved.The following is an abbreviated [+ modified to circumnavigate Forum formatting restrictions] version of the SUBTOTAL Functions Table:&nbsp;Function_num&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Function_num&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;Function(includes hidden values) &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;(ignores hidden values)&nbsp; 9 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 109 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;SUM&nbsp;My use is based on the above information, that offers to hide Cells, chosen by utilising "Format &gt; Cell &gt; Number &gt; Custom &gt; then inserting ';;;' appropriately".This action duly hides the visibility of the chosen Cell(s) content(s) but raises 2 questions:&nbsp;1. Is&nbsp;Function 9, designed NOT to display hidden&nbsp;content? This is occurring while Help Information implies that this is NOT true. The resulting Total certainly looks odd and the non-display of Values, given the simplicity of the maths, presents a Result that is visually incorrect.&nbsp;2. Is Function 109&nbsp;designed NOT to ignore hidden&nbsp;content&nbsp;with regard to the Total? Again this is occurring while Help Information implies that this is NOT true. Once again the non-display of Values presents a Result that is visually incorrect.&nbsp;Frankly I cannot believe this is or was the Developer's intention.&nbsp;Running on my setup strongly suggests dysfunctionality regarding these 2 SUBTOTAL Functions?&nbsp;As previously mentioned this Function, as described in Excel Help Information, is required on a working Spreadsheet. This Sheet has Rows containing 10+ Cells containing Numeric Values. ONLY one Product Type is affected but there are usually a few Varieties of that Type selected for C&amp;P.ONLY one Cell of each Product Type Row contains information that needs to be hidden.If the Values of these Cells are included in the Result output [SUM/Total Row/?] it is pointless hiding something that is not&nbsp;effectively hidden in reality.Rows are copied from the above Spreadsheet and pasted into another Sheet where the resulting Totals of several Rows are used for evaluation, the inclusion of false information negates the purpose of this process.&nbsp;Using Apple Numbers the desired Result can be achieved by a workaround, it is assumed this also would be possible in Excel. This simply relies on displacing the Cell, to be excluded from the Result, by moving it to a convenient adjacent Row. Then indicating this displacement by adding a simple graphic, in the form of a line with arrowheads on each end [as in: moved from here to here]. Hopefully reminding that a displacement has occurred by indicating the&nbsp;locations of both Cells. Rows containing information regarding this Product Type cannot include information displaced [read: hidden] because it is absent from the Rows that'll ultimately be C&amp;P.As this applied to a single Product Group the Graphics were easy to Group, but less easy to manipulate, lock in place or maintain, this unsatisfactory situation caused me to consider returning to Excel.&nbsp;Given the unanswered aspects regarding whether Microsoft actively support macOS running on M1 Chips, it would be very useful if some kind soul could replicate the attached Spreadsheet [in the Post at the top of this Thread] using a Windows system and post the outcome below.&nbsp;Please help me to justify subscribing to 365. Thanks in advance for any help you can offer.&nbsp;&nbsp;

steve_k_excel · Answer

Thanks for the detailed description of your scenario. I think the descriptions in the help article can be improved. The important point is to know how SUBTOTAL defines "hidden".  For a row to be considered manually hidden, it means that the entire row is not shown. You can do this by selecting a row, then right-click on it and choose "Hide" from the menu. If you can see the row at all, then it's not hidden, even if you've set the format of the cell so that the value in the cell is not visible. It would be better for the help to say that function_num 9 includes hidden "rows" rather than hidden "values" and that 109 ignores or excludes hidden "rows" rather than "values". If the range has a filter applied, then the values in the hidden rows will not be included by either of the function_nums. Here's an article about filtering - https://support.microsoft.com/en-us/office/filter-data-in-a-range-or-table-01832226-31b5-4568-8806-38c37dcc180e. The Office apps are supported on Mac OS running on M1 chips - https://support.microsoft.com/en-us/office/microsoft-365-office-2021-and-office-2019-support-for-apple-silicon-c55b603e-14a6-4b69-bdc0-2bb4c9a36834.

Forum Discussion

Please tell me what I am doing incorrectly-Excel SUBTOTAL Function 9 and 109 return the same Results

7 Replies

Resources