+ Reply to Thread
Results 1 to 2 of 2

payroll document labour summary per day, sum a column with condtitions

  1. #1
    Registered User
    Join Date
    09-02-2012
    Location
    auckland, New Zealand
    MS-Off Ver
    Excel 2010
    Posts
    8

    payroll document labour summary per day, sum a column with condtitions

    I am trying to create a labour summary sheet to show a dept what it costs them to run each day.

    this means looking at the hours worked per day (HEAT SHEET. Mon = Column H) and then multipling it by a pay rate ( HEAT SHEET. Column C) a sumproduct formula works perfectly for that but now i need to add some other conditons to it.

    we have pay codes if someone is on leave, works a stat day etc and these change what is included in the summary costs for that day.

    i have created a formula that works to determine the $$ amount that it costs to have a single person working on a day
    =IF(H5="","",IF(OR(D5="al",D5 ="LS"),(C5*0)*H5,IF(OR(D5="sw",D5="NL"),(C5*1.5)*H5,C5*H5))) (this is sitting out in col AR)

    what i need to do now is apply a formula or code ( i would rather populate the summary sheet on the press of a button) that will loop thru the column and add up each single persons costing till there are no more people to add. ( till column B is blank)

    would love some help please.

    The summary figure for heat for monday goes in cell c31 on the summary tab.

    have attached the summary page and one dept data sheet ( HEAT)heatsummary.xlsx

  2. #2
    Registered User
    Join Date
    09-02-2012
    Location
    auckland, New Zealand
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: payroll document labour summary per day, sum a column with condtitions

    heatsummary.xlsmOk so i have been self teaching myself and so far i have written the code below, which is returning the individual values i want and putting them in the range "FirstTargetCell".

    what i really want to do now is put one figure on the summary sheet in "C30" that sums all the figures or the FirstTargetCell, thats easy but i want to do it another way.

    i dont want to use FirstTargetCell but rather within the code i have written, say i create "C" and C=0 to start with, at the end of the 1st loop of IF statements in the code i want "C" to equal the result that would have been placed in the first cell, then after the second loop of IF statements C=C+ (the next result)

    Which at the end of all the loops C= Total days labour for heat and i want this value in "C30"

    Updated spreadsheet attached.

    Please Login or Register  to view this content.

+ 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