Automatically Autosum

[shiftyspina]

Hi

I don't think this is possible, and believe me I have tried, but I have built a sheet that has around 10,000 rows a week where I want to autosum subtotals without manually having to do it, and cant figure out the best way......

I have attached an example and what I am trying to do , and would appreciate any advice on how this is possible, as im lost and on the verge now of giving up with this

Thank you in advance

Dan

[Richard Buttrey]

Hi,

You are making life more complicated than it is by using merged cells. Most of us avoid them like the plague since they cause far too many problems. You've hit one of them.

You are making the same mistake that I see time and time again. You are mixing up the two elements of data capture and final reporting. The two require quite different treatments. A lot of people start by designing the form that they expect to see as the final report, and then wonder why it's so difficult to subsequently analyse and summarise or extract information from it. Yours exhibits all those features.

You should always capture data in a simple two dimensional table and worry about reporting information from it afterwards. Without exception doing this you will always be able to easily obtain management information. Rarely is this the case if you start the other way round.
You will also throw open the whole wonderful world of the powerful Pivot table functionality.

So before working out a way to handle this complex arrangement of data let me ask a basic question. Is it possible to change your layout slightly so that every row in columns A & C has a value (it's not clear to me why you've also joined column A to B)?

[shiftyspina]

Hi Richard

This report is coming from a SharePoint site, where it is a final report being built upon a mass database sitting within my network, unfortunately I can not get to that database, and can only get access to this report, the last three columns I have built to work off of this report using formulas, and have had to use iferror formulas and if cells <>0 to get it to do what I want, but the subtotalling and auto sum is becoming an issue.

it may better just to get the report to include these columns in SharePoint rather than me using formulas, unfortunately though with SharePoint it comes out as static figures, and the aim of this is to change the numbers in the new price column to then give a new profit figure......

So in short.... I cant change the report, I appreciate your quick response and agree with everything you have said, for now maybe I should not worry about this and just move on to the next task! haha

[Richard Buttrey]

Does the attached help.

I added two extra columns AV & AW and created a pivot table using AU:AW