Formula to add two cells when one or both cells are text
 

I created a multi-page workbook to determine the sales bonus earned by
my employees. The problem is that some of them forfeit their bonus if
they don't meet the minimum standards for quality, adherence,
retention, etc. Also, if someone has not made a sale or attempted
sale, it doesn't return a value at all rather it gives the result
#DIV/0!. Can someone help me write a formula that will assign a value
of 0 to a cell that contains the text "Forfeit" or the formula result
"#DIV/0!" and then add the resulting values in the two cells?
This is what I am looking at:

Name OH bonus MI bonus Total Bonus
Sales rep 175.50 #DIV/0!
Sales rep Forfeit #DIV/0!
Sales rep Forfeit #DIV/0!

Thank you for your help!

Formula to add two cells when one or both cells are text
 

Let's assume the OH bonus is on sheet 2...
=if(sheet 2!A1="Forfeit",0,sheet 2!A1)
Let's assume the MI bonus is on sheet 3...
=if(sheet 3!A1=0,0,sheet 3!A1/sheet 3!A2)
Total Bonus...
=B1+C1

Hope this helps.
" wrote:

I created a multi-page workbook to determine the sales bonus earned by
my employees. The problem is that some of them forfeit their bonus if
they don't meet the minimum standards for quality, adherence,
retention, etc. Also, if someone has not made a sale or attempted
sale, it doesn't return a value at all rather it gives the result
#DIV/0!. Can someone help me write a formula that will assign a value
of 0 to a cell that contains the text "Forfeit" or the formula result
"#DIV/0!" and then add the resulting values in the two cells?
This is what I am looking at:

Name OH bonus MI bonus Total Bonus
Sales rep 175.50 #DIV/0!
Sales rep Forfeit #DIV/0!
Sales rep Forfeit #DIV/0!

Thank you for your help!

Formula to add two cells when one or both cells are text
 

=SUM(IF(NOT(ISERROR(B2:C4)),B2:C4))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

wrote in message
oups.com...
I created a multi-page workbook to determine the sales bonus earned by
my employees. The problem is that some of them forfeit their bonus if
they don't meet the minimum standards for quality, adherence,
retention, etc. Also, if someone has not made a sale or attempted
sale, it doesn't return a value at all rather it gives the result
#DIV/0!. Can someone help me write a formula that will assign a value
of 0 to a cell that contains the text "Forfeit" or the formula result
"#DIV/0!" and then add the resulting values in the two cells?
This is what I am looking at:

Name OH bonus MI bonus Total Bonus
Sales rep 175.50 #DIV/0!
Sales rep Forfeit #DIV/0!
Sales rep Forfeit #DIV/0!

Thank you for your help!