Need Method to Sum text data

June 25, 2011 at 19:56:25
Specs: Windows XP
Thanks. I am trying to develp a schedule where I will show the initials of the persons covering the shift hours, hour by hour and I then want to add this column up and find out what the cost was to cover this schedule. I have been trying to find a way to place a numeric value to each set of initials, ie hourly pay, then when I add up the column initials, I will see the total cost. Using @ sum(C6:c9) to add the column, but I have yet to find a way to give the MT, AA, AT, V, SC values so that when the @sum is used, it returns a value. I found "define names" and can set the value for the MT, AA, etc, but the @sum still does not work. If I type +MT+AA, I get the proper answer. Would appreciate it if someone could set me straight, or direct me to some other key works so I can find something else in the help menu. I have been there repeatedly and I am still stuck. First time user here, so thank you in advance. G

See More: Need Method to Sum text data

Report •

June 26, 2011 at 06:15:52
1 - When you post to help forum such as, please use a subject line that is relevant to the contents of your post. If everyone used a generic subject line such as "Need help with Excel", we wouldn't be able to tell one question from another.

2 - I'm not sure why you are using the @ sign with SUM. The common/correct operator is the equal sign. In fact, when you use @, Excel changes it to = in both Excel 2003 and 2010 on my machine.

3 - If you will always be summing contiguous ranges (e.g. C6:C9) one possible solution would be to put the numeric values in a hidden column and then SUM your initials using OFFSET.

For example, if your initials were in C6:C9 and your assigned values were in D6:D9, this would allow you to highlight the initials by dragging and SUM the values:


The values in both Column D could be populated from a table using VLOOKUP so that you would be able to change the value (e.g. "hourly pay") in the table and not have to access the hidden column.

Unfortunately, this method will not work to SUM non-contiguous cells (C6+C8+C9) so if you will need to do that, come on back and we'll see what we can do.

(I don't have a quick solution to that and I don't have time to work on it right now, especially since I don't know if you will every need to do that.)

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

June 27, 2011 at 16:28:42
Thanks, never even heard of the idea of summing a hide column. Regarding the post, I will be more specific next time - right now I can not recall how I got started with the site, as I had to set up an account. I ended up using 'countif' statements, looking to see if each cell had certain initials, then I counted them and then multiplied the count results by the pay rate which I did have on a separate sheet, allowing changes to it if need be. The hidden column would have been better, as the statement that I had to create was long, having to check for 8 differeent people on the payroll. But it worked and I got practice writing these complex statements.
But thanks for your input, hidden column. Excel is so powerful, just need to learn these tricks. Thanks. I saw many responses from you when I searched, so you are very active - appreciated - Gene

Report •

Related Solutions

Ask Question