Excel Subtotal not working correctly

Question

Hi
I have this weird issue, where Subtotal formula is not updating its value, when a row is hidden. The file is stored in Onedrive and accessed via a browser. It was working fine as an offline document (on my local PC). What's weird is that it is not updating only when hiding rows (value doesn't decrease), but it is updating when unhiding rows (value increases). When I enter the cell and press enter, it updates properly. When I choose the cell and hover over the formula, it shows the correct value. But the cell value is displayed wrong. I have a next formula tied to this value, which therefore shows incorrect value (it compares its value to another cell and if it doesn't match, it prompts a notice).

I have other subtotal formulas on the same sheet for the same rows and they are working fine. Although I have them in sumproduct formula where it adds up only the values that match a certain criteria. Calculation options is set to automatic. I am using 109 method. I have tried different browsers, result is the same. I downloaded the very same file from Onedrive to my computer and tried, then it works as it is supposed to.

Anyone has any clue?

Answer 1

Hello,Madis Tago

Welcome to the Microsoft community!

I have realized that your Excel Subtotal not working correctly.

We ran a test and found the same problem as you did.

This may be due to the difference between OneDrive web-based desktop

 Since editing in OneDrive uses the Excel for web program.

For desktop use, you can use Excel for Microsoft 365, which is generally more comprehensive than Excel for web.

However, you can't rule out the possibility that editing in OneDrive may cause problems due to synchronization.

At the same time, I also confirmed the official description of the Excel Subtotal, the official explicitly mentioned the content of this article, the content of the figure is as follows.

 SUBTOTAL function - Microsoft Support

Thank you for your careful observation and suggestions, your ideas and suggestions are crucial to improving Microsoft products, but this forum is mainly a technical communication platform, the Microsoft development team will not pay attention to this forum.

In response to this, I would suggest that you can send feedback to Microsoft.

If you have any ideas or suggestions about Microsoft Office applications you can post them on the specialized feedback platform Excel · Community (microsoft.com), where specialized developers will pay attention to and comment on your feedback. After sending your feedback, you can share the feedback link in this forum so that other users with similar issues, including me, can vote for your feedback and the more votes you get the more likely you are to get attention from the development team.

Feel free to post back if you need further assistance. 

Thank you for your understanding and patience and I look forward to hearing from you.

Ryan - MSFT | Microsoft Community Support Specialist

Answer 2

Thank you for quick reply!

Reason for using web based Excel instead of Windows version is that this spreadsheet is used by more than one people on different computers.

I was playing around and figured out a workaround. Now instead of =SUBTOTAL(109;$H$9:$H$2007) I am using =SUMPRODUCT(SUBTOTAL(109,OFFSET($H$9,ROW($H$9:$H$2007)-ROW($H$9),,1))) and it is updating properly in web based Excel.

Answer 3

Hello,Madis Tago

I'm glad your issue was resolved. Thanks also for analyzing this issue so that community users experiencing the same problem can see how easy it is to fix it!

Feel free to post back if you need further assistance. 

Thank you for your understanding and patience and I look forward to hearing from you.

Ryan - MSFT | Microsoft Community Support Specialist