Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
|
|||
|
|||
Quote:
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Shift Schedule Formula | Excel Discussion (Misc queries) | |||
Zero values not legend, Value grater then zero show data and legend | Excel Discussion (Misc queries) | |||
Shift constant values in a range left or right by n columns | Excel Programming | |||
Inserting greek symbols in the legend ? | Charts and Charting in Excel | |||
How could insert symbols e.g. micro in chart legend in Excel? | Charts and Charting in Excel |