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>
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
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")
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,150
Members
448,552
Latest member
WORKINGWITHNOLEADER

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
Back
Top