# 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
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

### 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
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
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 .

#### Fluff

##### MrExcel MVP, Moderator
You're welcome & thanks for the feedback

#### Roumen Roussev

##### New Member
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
Try
=SUMPRODUCT(((D5:AH5=\$A\$35:\$A\$63)*(A5=\$C\$35:\$C\$63)*(\$N\$35:\$N\$63))*24)

#### Roumen Roussev

##### New Member
You are excel genius. Thanks a lot.

#### Fluff

##### MrExcel MVP, Moderator
You're welcome & thanks for the feedback

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...