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 thanks

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.

     

     

  • Haytham Amairah's avatar
    Haytham Amairah
    Silver Contributor
    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.

     

     

    • Titchard Family's avatar
      Titchard Family
      Copper Contributor
      Nov 30, 2017
      Many thanks this works like a charm. How would I do it if wanted to add 5 if there is any text other than yes?
  • 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