Add 5 if a cell contains text "yes"

[Titchard Family]

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

[#2 · Accepted solution]

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.

[#3]

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

[#4]

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?

[#5]

You can also refer it to a cell as follows:

[#6]

Thanks I understand that part but for example. If I requested a Yes and No answer and someone enters Yea. I would like to add up all answers that were not No.

[#7]

In the formula edit mode, replace this operator > with this =

Before

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

After

=IF(SUM(IFERROR(SEARCH(E8,B1:B10),0))=0,SUM(B1:B10)+5,SUM(B1:B10))

[#8]

Hi, to all!

You can use this formula (non CSE formula):

=SUM(B1:B10,5*COUNTIFS(B1:B10,"?*",B1:B10,"<>No"))

Blessings!

[#9]

Hi,

Is this what you wanted?

Rgds.

Hitesh Gaur