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

Copper Contributor

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.

7 Replies

@Gelphyn 

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.

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.

@Gelphyn 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". 

@Steve_K_Excel 

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:

 

From Excel > Help > SUBTOTAL [NB. Help Information has been Italicised] :

The SUBTOTAL function syntax has the following arguments:

  • Function_num     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:

 

Function_num                                     Function_num                                  Function
(includes hidden values)                   (ignores hidden values)

  9                                                         109                                                    SUM

 

My use is based on the above information, that offers to hide Cells, chosen by utilising "Format > Cell > Number > Custom > then inserting ';;;' appropriately".

This action duly hides the visibility of the chosen Cell(s) content(s) but raises 2 questions:

 

1. Is Function 9, designed NOT to display hidden 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.

 

2. Is Function 109 designed NOT to ignore hidden content 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.

 

Frankly I cannot believe this is or was the Developer's intention.

 

Running on my setup strongly suggests dysfunctionality regarding these 2 SUBTOTAL Functions?

 

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

 

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

 

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.

 

Please help me to justify subscribing to 365. Thanks in advance for any help you can offer. 

 

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

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

 

@

 

 

Microsoft have an excellent system for accessing Help Articles, that could possibly be tweaked by removing the need to Type in the Search Question and then Tap on an Option to view what has just been Typed In? Opening the Information to the Search Question directly from the Typed Input seems a better option?

The Information Displayed in Help Articles is read by a very wide range of Users from complete novices to experts. Those near the lower end of the learning ladder read what is available without intricate knowledge of everything related to their immediate needs.

In the matter under discussion SUBTOTAL Functions 9 and 109 do not perform as may be expected by following the directions offered. 

I will have to find an alternative means of meeting my objective in this project, or fiddle and fudge as is current practice.

In the above I hit Post and an Invalid Authentication Warning appeared.
Checking that I was Signed In cleared the way for me to Post but the first part is missing:

@Steve_K_Excel
Thank you very much for the Article Link confirming Microsoft support for Apple's M1 Chips.
The time spent on creating the Article would have been best spent on adding the information to the appropriate System Requirements documentation. In the latter position it would quickly be available to an audience of intending Users.

Microsoft have an excellent..............................