+ Reply to Thread
Results 1 to 6 of 6

SUM of Shift-wise and of both the Shifts

  1. #1
    Forum Contributor
    Join Date
    07-19-2017
    Location
    Mumbai
    MS-Off Ver
    2013
    Posts
    206

    SUM of Shift-wise and of both the Shifts

    Dear Excel Experts,
    I have a list of contractors, day, shift-wise movement i..e Performed TEUs and with using IF and SUMIFS I have to calculate Performed TEUs upon selection of..

    Specific contractor or all contractors, from shift and to shift, Specific Shift or Both the Shifts Performed TEUs; it's done for all combinations excluding Both the shifts.

    For example
    Case 1
    Contractor : Casby Logistics
    From Date : 30-Apr-18
    To Date : 02-May-18
    Shift : Day
    Performed TEUs : 5229

    Case 2
    Contractor : All TT Contractors
    From Date : 30-Apr-18
    To Date : 02-May-18
    Shift : Night
    Performed TEUs : 10459


    Case 3
    Contractor : Priti Logistics

    From Date : 30-Apr-18
    To Date : 02-May-18
    Shift : Day
    Performed TEUs : 5229

    Case 4
    Contractor : All TT Contractors
    From Date : 30-Apr-18
    To Date : 02-May-18
    Shift : Both
    Performed TEUs : 17390

    As per formula in L2 cell, all combinations are working excluding Both Shifts., kindly help to resolve the same.

    Thanks in advance.

    Kravindra
    Attached Files Attached Files

  2. #2
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: SUM of Shift-wise and of both the Shifts

    Here's the code for the sheet module:

    Please Login or Register  to view this content.
    And here's the book with the code installed:
    Attached Files Attached Files
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

  3. #3
    Forum Contributor
    Join Date
    07-19-2017
    Location
    Mumbai
    MS-Off Ver
    2013
    Posts
    206

    Re: SUM of Shift-wise and of both the Shifts

    Hi xladept,

    Thanks for your prompt response and great code script,

    TEUs should be change after changing following columns..
    Contractor | From Date | To Date | Shift


    As per macro defined by you, it is considering only Contractor , for calculating TEUs remaining criteria are to be caonsidred| From Date | To Date | Shift


    In case of All TT Contractors : TEUs are to be calculated on account of all 4 contractors ie. Casby Logistics, Priti Logistics, Royal Translines, Sudin Enterprises

    In case of Both Shift: TEUs are to be calculated on account of both the Shifts i.e. Day & Night

    Kindly relook and revert.

    Kravindra

    Thanks in advance for your code.

  4. #4
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: SUM of Shift-wise and of both the Shifts

    Try this:

    Please Login or Register  to view this content.

  5. #5
    Forum Contributor
    Join Date
    07-19-2017
    Location
    Mumbai
    MS-Off Ver
    2013
    Posts
    206

    Re: SUM of Shift-wise and of both the Shifts

    Fantastic !! Hat's off to you Boss!
    You are really Charm !!
    Thanks your great work.

    Kravindra

  6. #6
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: SUM of Shift-wise and of both the Shifts

    You're welcome!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Day and Shift wise allocation from Roster / employee shift schedule
    By Ravi_Kadu in forum Excel Programming / VBA / Macros
    Replies: 22
    Last Post: 01-11-2021, 07:40 PM
  2. Shift Management Changing shifts based on last shift of current month
    By zenod in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-22-2018, 04:06 AM
  3. [SOLVED] Shift Differential for 3 shifts
    By spirogeisel in forum Excel General
    Replies: 0
    Last Post: 03-16-2016, 01:51 PM
  4. Calculation of shift allowance affected by overtime and overnight shifts
    By danb1985 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-09-2014, 06:16 AM
  5. Track employees which cross shifts and counted one time each shift
    By Macileo in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 10-24-2013, 02:20 AM
  6. [SOLVED] I want change the data row wise to coloum wise & coloumn wise to row wise.
    By satputenandkumar0 in forum Excel General
    Replies: 3
    Last Post: 12-20-2012, 08:34 AM
  7. Shift roster for 3 shifts
    By shivaraj in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-24-2011, 06:35 AM

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