Forum Discussion

Copper Contributor

Jul 20, 2022

Function for adding values to a cell if text is a certain word

I have two columns which have either a "Yes" or "No" in them. If either of these columns have "Yes" written in them, I want a value of 10 in a separate cell(for example C2). If both of these are "Yes...

excel

Formulas and Functions

OliverScheurich

Gold Contributor

Jul 20, 2022

Ajinkya123

=IF(COUNTIF(A2:B2,"yes")=1,10,IF(COUNTIF(A2:B2,"yes")=2,20,""))

You can try this formula.

mtarler

Silver Contributor

Jul 20, 2022

alternatively:
=10*((A2="yes")+(B2="yes"))
or using dynamic arrays:
=10*((A2:A10="yes")+(B2:B10="yes"))