+ Reply to Thread
Results 1 to 5 of 5

sum up the values of shift schedule symbols from a range that matching the value legend

  1. #1
    Registered User
    Join Date
    08-14-2012
    Location
    Sofia
    MS-Off Ver
    Excel 2010
    Posts
    4

    sum up the values of shift schedule symbols from a range that matching the value legend

    I have a shift schedule with symbol and legend with duration value of each shift symbol. I want to summarize the value corresponding to the symbols for each employee.

    Now I use:
    =COUNTIF($D5:$AH5;$A$13)*($N$13*24)+COUNTIF(D5:AH5;$A$14)*($N$14*24)+COUNTIF(D5:AH5;$A$15)*($N$15*24)
    where
    D5:AH5 is range with shift symbols
    A13:A15 is range for symbols fom legend
    N13:N15 is range of corresponding symbols value with "hh:mm" formatting

    This is the WeTransfer link to an Excel file.

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: sum up the values of shift schedule symbols from a range that matching the value legen

    Hi and welcome to the forum.

    Please upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context.

    Show a before and after situation with manually calculated results, explaining which information is data and which is results, and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    08-14-2012
    Location
    Sofia
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: sum up the values of shift schedule symbols from a range that matching the value legen

    I apologize for the limited information, but when I tried to upload a workbook with the first post I was restricted.
    I try to summarize the sum of the symbols from a shift schedule taking the legend values where for each character there is a value of the corresponding row in another column.
    Each cell is a dropdown menu that receives information from the legend with the symbols.
    That's what I need to be able to dynamically change the symbols and get the right result.
    In the second stage I will try to make a button that automatically inserts a new line by keeping the formulas and deleting the symbols.
    Link to workbook: Schedule NEW - 2.xlsm

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,422

    Re: sum up the values of shift schedule symbols from a range that matching the value legen

    This is a 'low tech' proposal.
    Insert an extra row below each of the rows with names.
    Each of these 'extra' rows is populated using:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    The hours column is populated using: =SUM(D6:AH6)
    Note that the 'extra' rows may be hidden for aesthetic purposes.
    Note that rows 8 and 10 are hidden (using grouping), however row 6 isn't for the purpose of demonstration.
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  5. #5
    Registered User
    Join Date
    08-14-2012
    Location
    Sofia
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: sum up the values of shift schedule symbols from a range that matching the value legen

    Quote Originally Posted by JeteMc View Post
    This is a 'low tech' proposal.
    Insert an extra row below each of the rows with names.
    Each of these 'extra' rows is populated using:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    The hours column is populated using: =SUM(D6:AH6)
    Note that the 'extra' rows may be hidden for aesthetic purposes.
    Note that rows 8 and 10 are hidden (using grouping), however row 6 isn't for the purpose of demonstration.
    Let us know if you have any questions.
    Thanks but I found more elegant solution.
    =SUMPRODUCT((D5:AH5=A13:A34)*(N13:N34))*24
    Anyway thank you very much for the effort. I appreciate it.

+ 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. Replies: 0
    Last Post: 10-27-2014, 11:54 PM
  3. Making symbols appear in the chart legend
    By pickslides in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 04-03-2014, 03:45 AM
  4. Insert legend symbols as text
    By chobes in forum Excel General
    Replies: 0
    Last Post: 09-26-2012, 06:07 AM
  5. Shift Schedule - 3 Shift Coverage - Auto Populate Roll-up Summary Schedule
    By chips1256 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 08-31-2012, 09:32 AM
  6. Adding symbols and subscripts in legend
    By ady_mech in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 12-06-2007, 12:37 AM
  7. Inserting greek symbols in the legend ?
    By Lukas Barchewitz in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 06-23-2005, 11:05 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