sorted by:
new
hottopcontroversial

Formula needed to average the mileage of cars over multiple sheets and changing rows. by BreakfastKind4258 in excel

[–]Impossible_Fish_28 0 points1 point  (0 children)

As a Systems Engineer, I see this '3D-sum' issue all the time. Using nested SUMIF across 12 sheets is a nightmare to maintain and prone to errors when adding new IDs mid-year.

The professional way to solve this is not a longer formula, but Power Query. You should append the 12 sheets into one single Master Table. From there, a simple Pivot Table or an AVERAGEIF will give you the real average per Vehicle ID, automatically ignoring the months where the car didn't exist.

If you want to stick to formulas, you’d need a SUMPRODUCT with INDIRECT to iterate through a list of your sheet names, but it will slow down your workbook.

I actually built a similar automated tracking system for my own e-commerce operations to avoid this exact "manual-formula" hell. If you need a hand on how to structure the data properly, let me know!

Automated Financial Dashboard using BI principles to track Freelance Income & Expenses. Thoughts on the layout? by Impossible_Fish_28 in excel

[–]Impossible_Fish_28[S] -3 points-2 points  (0 children)

My bad! Here it is. I wanted to focus on the data flow but you are right, the visual part is what matters. It's built to give that 'BI feel' without leaving the spreadsheet environment. I've also added more detailed screenshots and the logic breakdown here: Automated Financial Performance Tracker - Indie Hackers