Forum Discussion

Copper Contributor

Jan 10, 2022

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

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

microsoft 365

Steve_K_Excel

Former Employee

Jan 14, 2022

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.

Gelphyn

Copper Contributor

Jan 16, 2022

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.

Gelphyn
Copper Contributor
Jan 16, 2022
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..............................