IF at First You Don’t…

If at first you don’t succeed, try, try again. Or in the words of W. C. Fields:

“If at first you don’t succeed, try, try again. Then Quit. There’s no use being a damn fool about it.”

Well, when it comes to Excel, we are blessed with many IFs!  These versatile functions are some of the most useful tools in Excel. There are several types of IF to choose from, and each of them can make your life in Excel easier. Here are 6 Fabulous Flavors of IF:

 1.  IF: =IF(Condition, Value If True, Value If False) 
The good, old plain IF function is valuable as a stand-alone tool or in conjunction with other “nested” functions (which are Totally Cool…). You can use cell references for the “Value If True” or the “Value If False”, or insert words (e.g. IF(B3>25, “Success”, “Failure”). Important Note: Be sure to use quotation marks if inserting actual words instead of cell references. 

 2.  COUNTIF: =COUNTIF(Range, Criteria) 
For the Criteria, you can specify a cell, a simple number such as 10, or use a greater-than or less-than criteria such as “>20”. Important Note: Once again, be sure to use the quotation marks if doing a great-than or less-than analysis. Alternatively, you can refer to a cell that contains your criteria number (No quotation marks needed if you are using that method).

 3.  SUMIF: =SUMIF(Range, Criteria, SumRange) 
The Criteria is the same as in COUNTIF, but you have the option of applying it to a “SumRange”. These are the Actual Cells to add if the cells in the Range match the criteria (pretty amazing, eh?). If SumRange is omitted, the cells in Range are evaluated by criteria and consequently added.

4.   SUMIFS: =SUMIFS(sumrange,criteriarange1,criteria1, criteriarange2, criteria1, etc.)  Note: This is SUMIF Plural!  This function first introduced in Excel 2007 totals values that meet more than one condition. That makes it the perfect solution for summing values in many analysis scenarios. 

5.   IFERROR: =IFERROR(Value, Value If Error) 
Also introduced for the first time in Excel 2007, this combines the IF and ISERROR functions, and essentially gives you greater control with potential Error Messages. Rather than just accepting the sometimes cryptic and less-than-helpful default error messages that Excel dishes out, you can Customize your error messages for more clarity.

6.  IFNA:  = IFNA(value, NAvalue)

Added in Excel 2010, the IFNA function works similarly by returning a value you specify if your formula returns the #N/A error. You can obtain similar results using IFERROR(), but IFNA() considers only the #N/A error, rather than all errors.

If at first you don’t succeed, Try an IF Function!