+ Reply to Thread
Results 1 to 5 of 5

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

  1. #1
    Registered User
    Join Date
    01-10-2008
    Posts
    3

    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
    Last edited by londonp; 01-10-2008 at 10:50 AM.

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902
    In my tests, the Subtotals feature automatically added a Grand Total at the bottom... yours didn't?
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Registered User
    Join Date
    01-10-2008
    Posts
    3
    Quote Originally Posted by NBVC
    In my tests, the Subtotals feature automatically added a Grand Total at the bottom... yours didn't?
    thanks for the help and yes you're right, the subtotals tool does add a grand total at the bottom, but at the very bottom of all the pages. I am trying to get a grand total at the bottom of each individual page (payslip). My aim is that each employee will get a payslip with the grand total of all there seperate rates for the month on.

  4. #4
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902
    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.

  5. #5
    Registered User
    Join Date
    01-10-2008
    Posts
    3
    thankyou, i shall certainly give that ago tommorow and let you know the outcome.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1