COUNTIF Question

[KolbyPanda]

Greetings!
I have this formula to display "ALS" if a cell contains the word "Paramedic" anywhere in the string and display "BLS" if the string does not contain "Paramedic".

=IF(COUNTIF(A1:A16,"*Paramedic*"),"ALS","BLS")

What I wish to do is instruct the cell to display one of three options.

1. If the source cells contains the word "Paramedic" anywhere in the cell then display "ALS"
2. If the source cells contains the word "EMT" anywhere in the cell then display "BLS"
3. If the source cells are empty then display "Not Staffed"

The current formula works great for the first two options, I just cant figure out how to add the third one.

Thanks in advance!
- Kolby

 

[Foz1980]

Presuming cell A1 is your source, then use the below.
You can just drag the formula down to whatever row your data ends.

=IF(A1="Paramedic","ALS",IF(A1="EMT","BLS",IF(A1="","Not Staffed")))

 

[KolbyPanda]

Thank you! I can never thank this forum enough! People here are great!

 

[Special-K99]

@Foz1980; That wont work, OP is looking for "Paramedic" anywhere in the string
@kollyPanda

=IF(COUNTIF(A1:A16,"*Paramedic*")>1,"ALS",IF(COUNTIF(A1:A16,"*EMT*")>1,"BLS","Not Staffed"))

QUESTION: What result do you want if A1:A16 contain BOTH Paramedic AND EMT ?

 

[Finalfight40]

Special-k99

Wouldnt it be >0 or >=1 instead of >1?

 

[Foz1980]

@Foz1980; That wont work, OP is looking for "Paramedic" anywhere in the string
@kollyPanda

=IF(COUNTIF(A1:A16,"*Paramedic*")>1,"ALS",IF(COUNTIF(A1:A16,"*EMT*")>1,"BLS","Not Staffed"))

QUESTION: What result do you want if A1:A16 contain BOTH Paramedic AND EMT ?

Ahh yes, fair point. I had misunderstood the original question.

 

[Special-K99]

Special-k99

Wouldnt it be >0 or >=1 instead of >1?

Good point, we're all making mistakes today

 

[jtakw]

Special-k99

Wouldnt it be >0 or >=1 instead of >1?

Hi,

Actually, even that's not needed, this will do just as well:

=IF(COUNTIF(A1:A16,"*Paramedic*"),"ALS",IF(COUNTIF(A1:A16,"*EMT*"),"BLS","Not Staffed"))