+ Reply to Thread
Results 1 to 5 of 5

Is there a formula to find one phrase in a selection?

  1. #1
    Tiff1618
    Guest

    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. #2
    Ray A
    Guest

    RE: Is there a formula to find one phrase in a selection?

    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. #3
    Biff
    Guest

    Re: Is there a formula to find one phrase in a selection?

    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" <Tiff1618@discussions.microsoft.com> wrote in message
    news:219EB429-F90A-406E-A208-5C787ED70467@microsoft.com...
    > 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. #4
    Tiff1618
    Guest

    Re: Is there a formula to find one phrase in a selection?

    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" <Tiff1618@discussions.microsoft.com> wrote in message
    > news:219EB429-F90A-406E-A208-5C787ED70467@microsoft.com...
    > > 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. #5
    Tiff1618
    Guest

    Re: Is there a formula to find one phrase in a selection?

    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:AA10,"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" <Tiff1618@discussions.microsoft.com> wrote in message
    > news:219EB429-F90A-406E-A208-5C787ED70467@microsoft.com...
    > > 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 to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1