Excel countif Pulling a cell to do different things
Excel 2007
I have a string of cells with changing numbers and letters (which all means something .. not random.)
It's basically a schedule. If they take a sick day, they bet on S, if they take a partial sick day, they bet on PS. The problem is also that they also work during opening hours. They put it in this format: (number) / PS.
Now, if they were just letters, I could just do = countif (range, "S") to keep track of how many s / ps cells there are. How will I keep track if they are PS where it also has a number separated by a forward slash and then PS .... I also need to be able to use that number to add to the total. Is this possible or will I have to format different things to keep track of all of this.
source to share
Assuming this is similar to 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 simply sums the "S" records using the COUNTIF function. 2- add a hidden row under each real data row that will copy the numeric part of the PS records with just 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 sums the "PS" records by summing the hidden row from step 2. 3- add another column that sums everything just by summing the data row. which will automatically ignore text entries. 4- have a common column that adds these three columns up
If you don't want to see the full columns "S" and "PS", you can of course just hide them.
So at the end the sheet will 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
NTN ...
My quick solution:
-
pass the cell value to the CSTR function, so no matter what you enter, you will be working with a string.
-
analyze the information. Look for S, PS, or any other code you deem valid. Use the Left or Right functions if you need to look at a partial line.
-
check the number by checking the ascii value or try the CINT function which will only work if the string can be converted to an integer.
If you can show a sample of your cells with changing numbers and letters, I can help you. Hope it works.
- Mike
source to share