Equation Question

[aliciajunod]

I am working on a spreadsheet that will keep track of our employees Sick, vacation, and holiday times. Per the boss, it has to be in a certain format. I have prepared the spreadsheet - no problem. However, he wants to enter the sick time as 4S, and then run a search on just the cells that have S and have it automatically calculate on the spreadsheet.

How in the world would I go about doing that?

[ragulduy]

So you want to enter a load of values like:
4S
3
7S
8
12S
etc. into cells and then sum all the values that have S at the end of them?

The initial answer is format your sheet better... but having read that you are stuck with the format something like:
=SUM(IF(ISERROR(FIND("S",A1:A10,1)),0,1*LEFT(A1:A10,1))) as an array formula (confirm with ctrl+shift+enter) should add up the numbers from all the cells in A1:A10 that have an "S" in them. Although it won't work for more than one digit numbers.

[newdoverman]

You can cheat! Enter your data with the values in one column and the code in the adjacent column then select and format the two columns with white internal borders and fit the column widths to look like a single column. The data will have the look that he wants and the functionality that you require.