Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old December 3rd 18, 11:03 AM
Junior Member
 
First recorded activity by ExcelBanter: Dec 2018
Posts: 4
Default Sum up shift schedule symbols values 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

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.

This is the WeTransfer link to an Excel file. https://we.tl/t-eyGTJAH9KB

  #2   Report Post  
Old December 3rd 18, 02:04 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,731
Default Sum up shift schedule symbols values from a range that matching the value legend

Hi Roumen,

Am Mon, 3 Dec 2018 10:03:58 +0000 schrieb Roumen Roussev:

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. https://we.tl/t-eyGTJAH9KB


you must unmerge A17:A18. Then you can use:
=SUMPRODUCT(($A$13:$A$29=D5:AH5)*$N$13:$N$29)*24

Have a look for AJ5:AJ7:
https://1drv.ms/x/s!AqMiGBK2qniTgeAJF9321HfNoZKV1g


Regards
Claus B.
--
Windows10
Office 2016
  #3   Report Post  
Old December 4th 18, 01:00 PM
Junior Member
 
First recorded activity by ExcelBanter: Dec 2018
Posts: 4
Default

Quote:
Originally Posted by Claus Busch View Post
Hi Roumen,

Am Mon, 3 Dec 2018 10:03:58 +0000 schrieb Roumen Roussev:

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. https://we.tl/t-eyGTJAH9KB


you must unmerge A17:A18. Then you can use:
=SUMPRODUCT(($A$13:$A$29=D5:AH5)*$N$13:$N$29)*24

Have a look for AJ5:AJ7:
https://1drv.ms/x/s!AqMiGBK2qniTgeAJF9321HfNoZKV1g


Regards
Claus B.
--
Windows10
Office 2016
Thank you so much. The solution works perfectly for the case.


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Shift Schedule Formula Dan Excel Discussion (Misc queries) 1 January 14th 10 05:50 AM
Zero values not legend, Value grater then zero show data and legend Pamela[_3_] Excel Discussion (Misc queries) 1 January 28th 09 11:20 AM
Shift constant values in a range left or right by n columns Dirk Goldgar Excel Programming 2 July 21st 08 07:09 PM
Inserting greek symbols in the legend ? Lukas Barchewitz Charts and Charting in Excel 1 June 23rd 05 03:30 PM
How could insert symbols e.g. micro in chart legend in Excel? farmash Charts and Charting in Excel 4 May 1st 05 03:23 AM


All times are GMT +1. The time now is 04:58 PM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017