Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Is there a formula to find one phrase in a selection?
Hey again,
Is there a formula I can use to figure out if there is one specific phrase in a selection? I'm updating the attendance prgram at the school that I work at. Every student has their own attendance sheet in Excel, and each sheet sort of looks like this: |Monday| Period 1:| A | (A=Absent; S=Seat time) Period 2:| S | Period 3:| A | So basically, if they have one or more "S"s, we can count them as being present for the day. I need the formula to find out if there is an "S" in that column, and if there is, I need it to type an "S" in the attendance report, which sort of looks like this: DATE: |1|2|3| Student Name |S| | | Sorry if that got a little complicated. Is there any way to do this? |
#2
|
|||
|
|||
one way
=IF(COUNTIF(B1:B3,"S")=1,"S","A") Modify range as needed HTH "Tiff1618" wrote: Hey again, Is there a formula I can use to figure out if there is one specific phrase in a selection? I'm updating the attendance prgram at the school that I work at. Every student has their own attendance sheet in Excel, and each sheet sort of looks like this: |Monday| Period 1:| A | (A=Absent; S=Seat time) Period 2:| S | Period 3:| A | So basically, if they have one or more "S"s, we can count them as being present for the day. I need the formula to find out if there is an "S" in that column, and if there is, I need it to type an "S" in the attendance report, which sort of looks like this: DATE: |1|2|3| Student Name |S| | | Sorry if that got a little complicated. Is there any way to do this? |
#3
|
|||
|
|||
Hi!
=IF(COUNTIF(B2:B100,"S"),"S",not_defined) Not_defined means that you haven't defined what to do if there are no S's. Do you want an "A" for absent? =IF(COUNTIF(B2:B100,"S"),"S","A") Do you want to leave the cell balnk? =IF(COUNTIF(B2:B100,"S"),"S","") Biff "Tiff1618" wrote in message ... Hey again, Is there a formula I can use to figure out if there is one specific phrase in a selection? I'm updating the attendance prgram at the school that I work at. Every student has their own attendance sheet in Excel, and each sheet sort of looks like this: |Monday| Period 1:| A | (A=Absent; S=Seat time) Period 2:| S | Period 3:| A | So basically, if they have one or more "S"s, we can count them as being present for the day. I need the formula to find out if there is an "S" in that column, and if there is, I need it to type an "S" in the attendance report, which sort of looks like this: DATE: |1|2|3| Student Name |S| | | Sorry if that got a little complicated. Is there any way to do this? |
#4
|
|||
|
|||
Oh my god, you guys are brilliant. Thank you so much!
"Biff" wrote: Hi! =IF(COUNTIF(B2:B100,"S"),"S",not_defined) Not_defined means that you haven't defined what to do if there are no S's. Do you want an "A" for absent? =IF(COUNTIF(B2:B100,"S"),"S","A") Do you want to leave the cell balnk? =IF(COUNTIF(B2:B100,"S"),"S","") Biff "Tiff1618" wrote in message ... Hey again, Is there a formula I can use to figure out if there is one specific phrase in a selection? I'm updating the attendance prgram at the school that I work at. Every student has their own attendance sheet in Excel, and each sheet sort of looks like this: |Monday| Period 1:| A | (A=Absent; S=Seat time) Period 2:| S | Period 3:| A | So basically, if they have one or more "S"s, we can count them as being present for the day. I need the formula to find out if there is an "S" in that column, and if there is, I need it to type an "S" in the attendance report, which sort of looks like this: DATE: |1|2|3| Student Name |S| | | Sorry if that got a little complicated. Is there any way to do this? |
#5
|
|||
|
|||
Oh, one more question. This is rediculously complicated, I know, but... when
you use that formula, can you put another IF(COUNTIF) check in the "value if false" spot? Because if there isn't an "S" in the column, it needs to be able to check for a "P" or an "N" (independent study or not enrolled) and mark that instead. I've been trying to figure it out with the =OR formula but I keep getting errors. This is what I've been typing that's not working and I don't know why: =OR(IF(COUNTIF(AA8:AA10,"S"),"S",(IF(COUNTIF(AA8:A A10,"P"),"P","A")))) Any ideas? "Biff" wrote: Hi! =IF(COUNTIF(B2:B100,"S"),"S",not_defined) Not_defined means that you haven't defined what to do if there are no S's. Do you want an "A" for absent? =IF(COUNTIF(B2:B100,"S"),"S","A") Do you want to leave the cell balnk? =IF(COUNTIF(B2:B100,"S"),"S","") Biff "Tiff1618" wrote in message ... Hey again, Is there a formula I can use to figure out if there is one specific phrase in a selection? I'm updating the attendance prgram at the school that I work at. Every student has their own attendance sheet in Excel, and each sheet sort of looks like this: |Monday| Period 1:| A | (A=Absent; S=Seat time) Period 2:| S | Period 3:| A | So basically, if they have one or more "S"s, we can count them as being present for the day. I need the formula to find out if there is an "S" in that column, and if there is, I need it to type an "S" in the attendance report, which sort of looks like this: DATE: |1|2|3| Student Name |S| | | Sorry if that got a little complicated. Is there any way to do this? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can I Find one word in a formula | Excel Worksheet Functions | |||
Formula to find cell with data on a row | Excel Worksheet Functions | |||
Find and Replace Formula Links | Excel Worksheet Functions | |||
Can't find the right lookup formula for this | Excel Worksheet Functions | |||
IF & VLOOKUP FORMULA | Excel Worksheet Functions |