IF(OR Question

[The Animal]

Hi
If someone can help
If the words "Part Time" or "Casual" is found in M17 then AZ17*Y17 but if "Salary" is found in M17 use the value in AZ17.
Thanks for any assistance

Stephen

 

[shaowu459]

=IF(ISNUMBER(FIND("Salry",M17)),AZ17,IF(OR(ISNUMBER(FIND("Part Time",M17)),ISNUMBER(FIND("Casual",M17))),AZ17*Y17,"Please define"))

 

[pvr928]

Hi The Animal

Does this work for you:

=AZ17*IF(OR((IFERROR(FIND("Part Time",M17),0))=1,(IFERROR(FIND("Casual",M17),0))=1),Y17,1)

This assumes there are only three options in M17, being 'Part Time', 'Casual' and 'Salary', ie it does not check if 'Salary' is actually displayed.

Cheers

pvr928

 

[JoeMo]

You didn't tell us whether those words would be the entire cell content or just part of a longer string. This should work for either case. Replace the ?? in red with what you want to be returned if none of the three strings are in M17.

=IF(ISNUMBER(SEARCH("Salary",M17)),AZ17,IF(OR(ISNUMBER(SEARCH("Part Time",M17)),ISNUMBER(SEARCH("Casual",M17))),Y17*AZ17,"??"))

 

[jtakw]

Hi,

M1 formula assumes there are Only 3 possible words in M17, and are Not part of a longer Text String.
N1 formula assumes there are More than 3 possible words in M17, and are Not part of a longer Text String.
M2 formula assumes there are Only 3 possible words in M17, but may be part of a longer Text String.
N2 formula assumes there are More than 3 possible words in M17, but may be part of a longer Text String.

Book1
	M	N	Y	Z	AZ
1	90	90
2	90	90
16
17	Casual		10		9
Sheet112

Cell Formulas
Range	Formula
M1	=AZ17*IF(SUM(COUNTIFS(M17,{"Part Time","Casual"})),Y17,1)
M2	=AZ17*IF(OR(ISNUMBER(SEARCH({"Part Time","Casual"},M17))),Y17,1)
N1	=IF(M17="Salary",AZ17,IF(SUM(COUNTIFS(M17,{"Part Time","Casual"})),AZ17*Y17,"No Match"))
N2	=IF(ISNUMBER(SEARCH("Salary",M17)),AZ17,IF(OR(ISNUMBER(SEARCH({"Part Time","Casual"},M17))),AZ17*Y17,"No Match"))

 

[The Animal]

Thanks so much
Used Joe's as per below with a few additions and if none of the 3 variables exist return a ""
=IF(ISNUMBER(SEARCH("Salary",M17)),AZ17,IF(OR(ISNUMBER(SEARCH("Part Time",M17)),ISNUMBER(SEARCH("Casual",M17))),(Y17*AZ17)+(AL17+AI17),""))

 

[jtakw]

My N2 formula in Post #5 does the same and is a "little bit" shorter.

 

[The Animal]

Hi Guys
Seem to be getting a #Value from below, How would it look if I wanted cell to display "" if AZ4 is ""
=IF(ISNUMBER(SEARCH("Salary",M4)),(AZ4+BC4+BB4),IF(OR(ISNUMBER(SEARCH("Part Time",M4)),ISNUMBER(SEARCH("Casual",M4))),(Y4*AZ4)+(AL4+AI4),""))

Thanks Stephen

 

[JoeMo]

Try:

=IF(AZ4="","",IF(ISNUMBER(SEARCH("Salary",M4)),(AZ4+BC4+BB4),IF(OR(ISNUMBER(SEARCH("Part  Time",M4)),ISNUMBER(SEARCH("Casual",M4))),(Y4*AZ4)+(AL4+AI4),"")))

 

[The Animal]

Thanks Joe Brilliant