Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Oct 2001
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Counting (97now, soon 2003)

    Hello
    I use a spreadsheet at work to tally hours worked against shift type.
    Some staff who work a specific shift type require a specific form to be completed.
    I have a conditional formatting set up - when the shift type is worked the pay form number is placed in a cell to remind to complete said form.
    I have a sum(countif(... etc set up to count up number of shifts worked that require the pay form.
    How do I set-up to count the number of hours worked against when pay form is highlighted.
    Staff can work 2 different shift types in a day, one required said pay form while the other not.

    just on day D78 shift type D-Day , AD Admin etc
    E7:E8 hours worked to shift type
    F7:F8 meal break credited (if any)
    G8 sum of above
    G7 cell that is conditionally formatted to pay form number.

    I did try SUM(SUMIF(G7,"pay form Number",E7:E8),SUMIF( ... for next day..... very long formula
    but it does not recognise when different shift types are worked in the same day.

    Hope I have explained this OK

    Thanks for reading

    AJM

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Counting (97now, soon 2003)

    Could you post a small sample workbook with some dummy data?

  3. #3
    New Lounger
    Join Date
    Oct 2001
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counting (97now, soon 2003)

    Sorry for delay, posting this from work as was on sick leave.
    Attached is sample
    Thanks for looking at this.
    AJM

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Counting (97now, soon 2003)

    I don't understand your example
    a) Why shouldn't the 4 hours AD on Friday be included?
    [img]/forums/images/smilies/cool.gif[/img] Why should the 6 hours OTC on Friday be included? Should OTC always be included?

  5. #5
    New Lounger
    Join Date
    Oct 2001
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counting (97now, soon 2003)

    a + [img]/forums/images/smilies/cool.gif[/img] This example is for the boss. He is normally 'office hours' only (AD hours) but has to cover the 'Workers' who work a 24 hour shift roster (sick/leave cover), so any hours he works on the 24 hour roster including all overtime he gets paid an additional shift allowance (pay form 1943B) as the 'worker' get a shift allowance rate anyway as they on a 24 hour roster.
    Spreadsheet works fine if my boss works whole shift on 24 hour roster or all office hours but it does happen that some of day is office hours and rest of day is shift hours hence pay form 1943B for those hours worked.

    Hope this helps
    Regards
    AJM

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Counting (97now, soon 2003)

    I still don't understand. I thought the presence of 1943 in a row was required. It is present in row 17, but you don't want to count row 17. It is not present in row 18, but you want to include row 18.

    Can you state clear, precise and complete criteria for which rows should be included and which shouldn't?

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Counting (97now, soon 2003)

    How about: this array formula (confirm with ctrl-shift-enter):
    =SUM(IF(($G$7:$G$19="1943B")*($D$7:$D$19<>"AD"),$E $7:$E$19))+SUM(IF(($G$7:$G$19="1943B")*($D$8:$D$20 <>"AD"),$E$8:$E$20))

    It sums anything that has 1943B in the row or anything that has 1943B in the row above it as long as the item is not "AD"

    Steve

  8. #8
    New Lounger
    Join Date
    Oct 2001
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counting (97now, soon 2003)

    Steve, Hans
    Many thanks for helping out here. Steve your formula worked great. I am going to re design the workbook, I've complicated things somewhat in adding to it.

    Regards
    AJM

Posting Permissions

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