| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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! |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Text Wrapping Cells that are part of a formula | Excel Discussion (Misc queries) | |||
| checking that cells have a value before the workbook will close | Excel Worksheet Functions | |||
| Formulas dealing with text data | Excel Worksheet Functions | |||
| Can I create a formula from text in several cells? | Excel Worksheet Functions | |||
| Macro or Formula to remove Text from Cells | Excel Worksheet Functions | |||
Linear Mode
