Excel Formula to Calculate Running Total for Filtered Data Showing Zero for Hidden Rows
I need help solving I'm dealing with I've been struggling with this for a few days now and could really use some help... I'm trying to configure I've been struggling with this for a few days now and could really use some help. I'm working on a project and hit a roadblock. I'm trying to create a running total in Excel for a filtered list, but I'm working with an scenario where the formula returns zero for rows that are hidden by the filter. I'm using Excel 365 and I want to ensure the running total only calculates based on visible rows. Here's the formula I'm currently using to calculate the running total: ```excel =SUMIF($A$2:A2, "<>0", B$2:B2) ``` In this case, column A contains my criteria, and column B has the values for which I want the running total. However, when I apply a filter, the formula still includes hidden rows, which leads to incorrect totals. I've tried using the `SUBTOTAL` function, but it doesn't provide a running total that updates dynamically as I change the filter. I also attempted to implement a helper column that flagged the visible rows, but that complicates the calculations and still doesn't yield the expected results. Here’s the formula I used in the helper column: ```excel =IF(SUBTOTAL(103, OFFSET(A2, ROW(A2)-MIN(ROW(A:A)), 0, 1)), B2, 0) ``` This helper column works, but now I need to sum these values for the running total. Each time I filter, I want the running total to reflect only the values that are currently visible. Is there another approach or formula that can guide to achieve this without resorting to VBA? Any insights or best practices would be greatly appreciated! I'm working on a API that needs to handle this. Any help would be greatly appreciated! For reference, this is a production application. Is this even possible? I'm working in a Windows 10 environment. Cheers for any assistance! This is my first time working with Excel 3.10. Thanks for any help you can provide! Thanks, I really appreciate it! Is there a better approach? This is happening in both development and production on Debian. I'd be grateful for any help.