Calculating grand-totals using numerous sub-totals in seperate coloumns.

Hi,

Any help/soloutions for this query would be gratefully recieved.

I am using excel to create monthly payslips from information gathered from an SQL database program used by a transport company.

I am able to export the necessary data into excel (i.e. Employee Name, date, etc..) and then sepearte the data onto 1 page per employee using the sort and then subtotal tools.

This then gives me a "monthly payslip" per employee, which shows in each row an individual daily date column, a job description column, an employees name column and 5 rate columns. (all the employees are paid daily hence the need to display each days rate on a seperate row). There are 5 rate columns that correspond with the possible 5 rates of pay that an employee can earn depending on which job/site they are working on on any given day.

When using the subtotals tool to split each employee onto a seperate page (payslip) excel displays the monthly total for each rate column at the bottom of the page.

This is all fine but my query is how to then take the sub-total (as generated by excels subtotals tool) for each rate column, add them together and then display a grand total for the months earning at the very bottom of the page (payslip). I obviously wish to be able to do this automatically rather than using a sum and manually creating the grand-totals for each employee (+200!)

Please see below a link for an image of what i am trying to achieve. The bright green lines indicate a page break for each individual employees pay slip. The red box highlights the sub-totals that i want to add together to gain a grand-total which can then be displayed at the bottom of each page.

Any help would be fantastic, thankyou

http://img411.imageshack.us/img411/5...celhelpbl4.jpg

In my tests, the Subtotals feature automatically added a Grand Total at the bottom... yours didn't?

I don't think that is possible with the features Excel provides...

but possible you can use a formula in column I that will show a Total if the line contains those subtotals...

Try this formula in I2 and copied down the whole column....

Please Login or Register  to view this content.

it will display a sum only in rows where those subtotals exist.