How to sum up the values of the working schedule symbols from a range that matching the value legend for these characters

Roumen Roussev

New Member
Joined
Nov 21, 2018
Messages
6
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
Image: https://ibb.co/WpQfQyY

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

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,610
Office Version
365
Platform
Windows
Hi & welcome to MrExcel.
You could use
=SUMPRODUCT((D5:AH5=A13:A34)*(N13:N34))*24
but for it to work you will need to remove the merged cells A17:A18
you will also need a value of 0 in col N rather than ""
 

Roumen Roussev

New Member
Joined
Nov 21, 2018
Messages
6
Hi & welcome to MrExcel.
You could use
=SUMPRODUCT((D5:AH5=A13:A34)*(N13:N34))*24
but for it to work you will need to remove the merged cells A17:A18
you will also need a value of 0 in col N rather than ""
Thank you very much. The solution works perfectly without merged cells .
 

Roumen Roussev

New Member
Joined
Nov 21, 2018
Messages
6
I do not know whether it's right to continue the topic, but I'm faced with another problem in the formula.
I need to use the formula so as to summarize only if another condition for comparison is fulfilled in the second range. I have identical symbols that differ only by criteria in another column
I tried:
=SUMPRODUCT((AND((D5:AH5=$A$35:$A$63);(A5=$C$35:$C$63))*($N$35:$N$63))*24)
But it doesn't work.
This is WeTransfer link to an Excel file. https://we.tl/t-acRK0YoCGO
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,610
Office Version
365
Platform
Windows
Try
=SUMPRODUCT(((D5:AH5=$A$35:$A$63)*(A5=$C$35:$C$63)*($N$35:$N$63))*24)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,610
Office Version
365
Platform
Windows
You're welcome & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,102,266
Messages
5,485,762
Members
407,513
Latest member
Gregor Lockyer

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top