duplicate formulas in same cell.

**olsoniv** · 03-09-2011, 01:07 PM

Question for you wizards out there. I find myself having duplicate formulas in a cell a lot.

if(countifs(Sheet1!A:A,Sheet2!A2,Sheet1!B:B,Sheet2!B2)>0,countifs(Sheet1!A:A,Sheet2!A2,Sheet1!B:B,Sheet2!B2),"No Instances")

This is a simple example. I also use this to see if something evaluates to a #N/A or #REF (aka error handling) as well as other applications. Is there a way to evalulate a statement and put cell output in the cell based on one evaluation instead of nesting the formulas like this? Using something like substitute or any other function I am not familiar with?

thanks in advance.

**NBVC** · 03-09-2011, 01:15 PM

Excel 2007 and newer has an IFERROR(function,error_result) function that allows you to enter the evaluation function once.

You can perhaps add helper columns with simplified formulas, then base results on the helpers with simple formula like =Count() or simpler Countif().

Some functions like Countif, Countifs, Sumif, Sumifs, etc are efficient enough that it doesn't hurt to repeat them in the formula... if there is no simpler way to determine condition status.

**olsoniv** · 03-09-2011, 01:20 PM

Thanks NBVC. I can at least get rid of all those duplicates on error handling. So, with IFERROR, will that catch #N/A's, #REF's #NAME, and #VALUE?

**NBVC** · 03-09-2011, 01:22 PM

Originally Posted by olsoniv

So, with IFERROR, will that catch #N/A's, #REF's #NAME, and #VALUE?

yes All of those and more:

The following error types are evaluated: #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!.

**olsoniv** · 03-09-2011, 01:26 PM

I just tried to use iserror (I'm on excel 2010), it only has one input value and evaluates to true or false. Is there another version I should be using? I checked the online help and it only lists it has having one input value....