Assigning a value to text in cells and summing a number of cells

LL_Livo

New Member
Joined
Jan 15, 2017
Messages
8
HI there,

I am assisting in re-designing a work roster for our workplace. We have 200+ staff members and there are about 20+ shift codes in use which has hours assigned to ranging from 0 to 12 hours. As we allow staff to self roster on a central spreadsheet, we want to have a column to add up the text shift codes they put in, so that it can tally up to see if they are over or under their hours for the month.
I have mocked up an example of what I am trying to achieve. I want to know what formula I can do in column "Weekly Total" to add up for each staff member for that week. Eg: Joe Smith will be 46 hours.

I hope that my explanation makes sense. As you can imagine our Real life roster is massive, so trying to find an automated way to do this!

Thanks in advance, LL_Livo


Example: Shift A equals 8 hours, B1=12hrs, S=10hrs, X=0, L=8
<o:p></o:p>
23/01/2017<o:p></o:p>
24/01/2017<o:p></o:p>
25/01/2017<o:p></o:p>
26/01/2017<o:p></o:p>
27/01/2017<o:p></o:p>
28/01/2017<o:p></o:p>
29/01/2017<o:p></o:p>
Weekly Total<o:p></o:p>
Joe Smith<o:p></o:p>
A<o:p></o:p>
B1<o:p></o:p>
S<o:p></o:p>
L<o:p></o:p>
L<o:p></o:p>
X<o:p></o:p>
X<o:p></o:p>
<o:p></o:p>
Anne Jones<o:p></o:p>
L<o:p></o:p>
S<o:p></o:p>
B1<o:p></o:p>
L<o:p></o:p>
X<o:p></o:p>
X<o:p></o:p>
A<o:p></o:p>
<o:p></o:p>
<tbody> </tbody>
 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,691
Office Version
  1. 2010
Platform
  1. Windows
You could put this formula in cell I2 and copy it down...

=8*COUNTIF(B$2:H$2,"A")+12*COUNTIF(B$2:H$2,"B1")+10*COUNTIF(B$2:H$2,"S")+8*COUNTIF(B$2:H$2,"L")
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,409
Or maybe this

Create a table Code-Hours (see columns K:L)


A
B
C
D
E
F
G
H
I
J
K
L
1
23/01/2017​
24/01/2017​
25/01/2017​
26/01/2017​
27/01/2017​
28/01/2017​
29/01/2017​
Weekly Total​
Code​
Hours​
2
Joe Smith​
A​
B1​
S​
L​
L​
X​
X​
46​
A​
8​
3
Anne Jones​
L​
S​
B1​
L​
X​
X​
A​
46​
B1​
12​
4
L​
8​
5
S​
10​
6
X​
0​
7

Formula in I2 copied down
=SUMPRODUCT(SUMIF($K$2:$K$6,B2:H2,$L$2:$L$6))

Hope this helps

M.
 

LL_Livo

New Member
Joined
Jan 15, 2017
Messages
8
Thanks Rick, I just tried it and it works! Do you think there is a shorter solution, considering I have more than 20+ unique text strings to put in the formula?
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,409

ADVERTISEMENT

Have you seen my suggestion in post #3?

M.
 

LL_Livo

New Member
Joined
Jan 15, 2017
Messages
8
Hi Marcelo, I did just try it on the example one and it worked beautifully. However, when I tried to do the same on my proper roster, it didn't work.
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,409

ADVERTISEMENT

Hi Marcelo, I did just try it on the example one and it worked beautifully. However, when I tried to do the same on my proper roster, it didn't work.

Maybe there are extraneous characters (leading and/or trailing spaces ) in your data like A_ or _A_ or B1_ (where _ represents a space)


Tell us if this formula works
=SUMPRODUCT(SUMIF($K$2:$K$6,TRIM(B2:H2),$L$2:$L$6))

M.
 

LL_Livo

New Member
Joined
Jan 15, 2017
Messages
8
Maybe there are extraneous characters (leading and/or trailing spaces ) in your data like A_ or _A_ or B1_ (where _ represents a space)


Tell us if this formula works
=SUMPRODUCT(SUMIF($K$2:$K$6,TRIM(B2:H2),$L$2:$L$6))

M.

HI Marcelo, thank you, I did just try that and I didn't work either. :(
I have just private messaged you the spreadsheet of my real life roster, would you mind having a look to see what I am doing wrong?
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,691
Office Version
  1. 2010
Platform
  1. Windows
Hi Marcelo, I did just try it on the example one and it worked beautifully. However, when I tried to do the same on my proper roster, it didn't work.
Please Note
-------------------
For future questions you may ask, please do not simplify your question for us... doing so will almost always lead to you coming back for help when the solution we give you for the simplified question cannot be applied to your actual data and its layout. One thing you must keep in mind when you ask a question in a forum... the people you are asking to help you know absolutely nothing about your data, absolutely nothing about how it is laid out in the workbook, absolutely nothing about what you want done with it and absolutely nothing about how whatever it is you want done is to be presented back to you as a result... you must be very specific about describing each of these areas, in detail, and you should not assume that we will be able to "figure it out" on our own. Remember, you are asking us for help... so help us to be able to help you by providing the information we need to do so, even if that information seems "obvious" to you (remember, it is only obvious to you because of your familiarity with your data, its layout and the overall objective for it).



Thanks Rick, I just tried it and it works! Do you think there is a shorter solution, considering I have more than 20+ unique text strings to put in the formula?
Maybe show us the 20+ unique text strings so we know what you are working with.
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,409
Conditional Formatting doesn't matter. For sure you have problems with your data.
To clean up the data you can try the macro TrimALL - see this link
Rearranging Data in Columns

Alternatively you can upload your file to a free file-sharing site and post the link in the thread.
There are many in the internet - box.com, for example

BTW, please post any new question in the Thread (not PM). Doing so others members also can help

M.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,122,842
Messages
5,598,396
Members
414,235
Latest member
sucosama

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top