Forum Discussion

Titchard Family's avatar
Titchard Family
Copper Contributor
Nov 30, 2017

Add 5 if a cell contains text "yes"

Hi all   Is this possible?. I would like to add several cells together then add "5" to the total if one cell contains the text 'yes'.   I have so far   =SUM(B1,C1,D1,E1)   please help...
counting cells if
Formulas & Functions
  • Haytham Amairah's avatar
    Haytham Amairah
    Nov 30, 2017

    Hi,

     

    Yes, this is possible!

     

    Try this formula:

     

    =IF(SUM(IFERROR(SEARCH("yes",B1:B10),0))>0,SUM(B1:B10)+5,SUM(B1:B10))

    NOTE: To enter this formula press Ctrl+Shift+Enter, this keystroke will force the formula to return the right result.

     

     

Kaloyan Stoyanov's avatar
Kaloyan Stoyanov
Copper Contributor
Nov 30, 2017

Hi,

I'm sorry, but do you want to count or sum the cell values?

If you want to count the cells and add 5 to the total only once, you could try this - not the most elegant solution, but it works:

=COUNTA(<your range>)+IF(COUNTIF(<your range>,"Yes"),5,0)

You can substitute <your range> with the cells - either B1:E1, or 1:1 if you are looking to expand it.

If you're looking to sum, use Haytham's solution :)

KR

Resources