Forum Discussion
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...
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.
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.
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?
You can also refer it to a cell as follows:
- 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.
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))
