Excel countif Pulling apart a cell to do different things

Question

Excel 2007

I have a row of cells with variation of numbers and letters (which all mean something.. not random.)

It's basically a timesheet. If they take a sick day they put in S, if they take a partial sick day they put in PS. The problem is they also put in the hours they did work too. They put it in this format: (number)/PS.

Now if it were just letters I could just do =countif(range,"S") to keep track of how many s / ps cells there are. How would I keep track if they are PS where it also has a number separated by a slash then PS.... I also still need to be able to use that number to add to a total. Is it even possible or will I have to format things different to be able to keep track of all this stuff.

Answer 1

My quick take on this is:

• pass the cell value into a CSTR function, so no matter what is entered you will be working with a string.

• parse the information. Look for S, PS, or any other code you deem to be valid. Use Left or Right functions if you need to look at partial string.

• check for number by testing the ascii value, or trying a CINT function, which will only work if the string can be converted to integer.

If you can show a sample of your cells with variation of numbers and letters I can give you more help. Hope this works out.

-- Mike

Answer 2

Assuming this is something like what your data looks like:

    A B C D    E 
1   1 2 S 4/PS 8

...then you could do this:

1- add a column that just totals the "S" entries with a COUNTIF function. 2- add a hidden row beneath each real data row that will copy the numerical part of the PS entries only with this function in each column:

=IF(RIGHT(B1,2)="PS",IF(ISERROR(LEFT(B1,LEN(B1)-SEARCH("/",B1)-1)),"",INT(LEFT(B1,LEN(B1)-SEARCH("/",B1)-1))),"")

3- add another column to the right that just totals the "PS" entries by summing the hidden row from step 2. 3- add another column that totals everything by just summing the data row. that will ignore the text entries automagically. 4- have a grand total column that adds those three columns up

If you don't want to see the "S" and "PS" total columns, you can of course just hide them.

So in the end, the sheet would look like this:

    A B C D    E  F  G  H  I  J
1   1 2 S 4/PS 8     1  4  11 16
2         4        <--- hidden row

HTH...