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

[LL_Livo]

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></o>	23/01/2017<o></o>​	24/01/2017<o></o>​	25/01/2017<o></o>​	26/01/2017<o></o>​	27/01/2017<o></o>​	28/01/2017<o></o>​	29/01/2017<o></o>​	Weekly Total<o></o>
Joe Smith<o></o>	A<o></o>	B1<o></o>	S<o></o>	L<o></o>	L<o></o>	X<o></o>	X<o></o>	<o></o>
Anne Jones<o></o>	L<o></o>	S<o></o>	B1<o></o>	L<o></o>	X<o></o>	X<o></o>	A<o></o>	<o></o>

<tbody> </tbody>

 

[LL_Livo]

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




Maybe show us the 20+ unique text strings so we know what you are working with.

Thanks Rick for your advice, and my apologies for not approaching this correctly as I am new to the forum and new to asking for help with my limited Excel knowledge technical knowledge.

 

[LL_Livo]

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.

Thanks Marcelo, apologies for not approaching my question correctly. I will load the file onto a shared site and reply to this thread in the correct manner.

 

[LL_Livo]

Hi all again, Thanks Marcelo and Rick for your advice.


I am attaching a link to my file which I have extracted from my master that I am working on (removed names and other unnecessary sections).


https://www.dropbox.com/s/tgt6hp8ww06zt3f/Roster_2017_v110117_Sample.xlsx?dl=0

You can see in the "Roster" Worksheet, what I am wanting to do is add the values or hours assigned to each text string, over a fortnightly basis. The text are shift codes our employees can enter when they request what shifts they would like. For example Employee 1, over the course of the fortnight is requesting 6 shifts, which should tally up to 68hrs. I used Marcello's suggested code of SUMPRODUCT(SUMIF(.......), however I feel like I have done something wrong as it is not adding up correctly. I have created the Table of Shift Codes and Hours to worksheet "Codes and hours". Some of the codes are assigned "0" hours as they do not need to tally up to "Hours Working" in column P.


Any advice of what I am doing wrong using Marcello's suggested formula OR other suggested solutions would be much appreciated.

I Hope I have explained myself a bit better and you are able to access the file I have created to assist. Thanking you all in your time and advice.

 

[Marcelo Branco]

The cells P3:Pn have a custom format dd (???)
Change the format to General and you see the correct results!

M.

 

[Marcelo Branco]

What was going on with the cells formatted as dd?
dd is a typical format for Dates.So Excel was assuming the result as a date.

Dates in Excel are stored as numbers beginning in 01/01/1900 that corresponds to number 1. The result of the formula in P3 is 72, so Excel was assuming it as the 72th day of the series = 12/03/1900 (dd/mm/yyyy)

The format dd asks Excel to show only the Day of this date, that is, 12 - definitively not what you are looking for...

M.

 

[LL_Livo]

What was going on with the cells formatted as dd?
dd is a typical format for Dates.So Excel was assuming the result as a date.

Dates in Excel are stored as numbers beginning in 01/01/1900 that corresponds to number 1. The result of the formula in P3 is 72, so Excel was assuming it as the 72th day of the series = 12/03/1900 (dd/mm/yyyy)

The format dd asks Excel to show only the Day of this date, that is, 12 - definitively not what you are looking for...

M.

Thank you, thank you, Thank you Marcelo. Thankful for that easy fix!

I am not sure how that custom format came about, I did insert column in previous spreadsheet, next to a already populated column - that may have caused it, I am not sure!

Thanks again Rick and Marcelo for your patience with me today!