IF OR THEN Formula

[mittmje]

Need to create a formula that looks for a word in a row and returns a value based on the word found.
As an example:
IF the word resistor is in any column in the row THEN assign a value of $100,
OR IF the word capacitor is found in any column THEN assign a value of $150,
OR IF the word transistor is found in the row THEN assign a value is $200

Only one of the words would be in each row

[Ace_XL]

=IF(COUNTIF(6:6,"resistor"),100,IF(COUNTIF(6:6,"capacitor"),150,IF(COUNTIF(6:6,"transistor"),200,"")))

[mittmje]

very cool Thanks

[mittmje]

Only problem is if the formula is placed in the same row as the word I get a circular reference error. Any way to modify so the formula is in the same row as the word?

[FDibbins]

change then row ref to a range ref....6:6 to B6:E6 (aDJUST AS NEEDED)

[Tony Valko]

Here's another one:

=SUM(COUNTIF(B2:D2,{"resistor","capacitor","transistor"})*{100,150,200})

[mittmje]

thanks for the replies. I liked this one best

=SUM(COUNTIF(B2:D2,{"resistor","capacitor","transistor"})*{100,150,200})

Thanks again

[Tony Valko]

You're welcome. Thanks for the feedback!

[mittmje]

Ho do I mark this as solved?

[Tony Valko]

Immediately above the first post there is a menu bar.

Select Thread Tools and there will be a selection to mark the thread as solved.