No IFs About It…

There are no “IFs” about it, the “IF” Functions are some of the most useful and accessible utensils in Excel. In fact, the knowledge of IF functions is so critical to any Excel users set of tools that it bears an occasional review.

There are, of course, several types of IF to choose from in Excel. Each of these powerful gizmos can aid you in making sense of your data. Here are my current 5 Favorite Ifs:

1.  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 “>50”. 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 with that approach).

2.  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. If SumRange is omitted, the cells in Range are evaluated by criteria and added accordingly.

3.  SUMIFS: =SUMIFS(Sum_Range, Criteria_Range1, Criteria1, [Criteria_Range2, Criteria2], ...)

First introduced in Excel 2007, but often overlooked, this function is similar to SUMIF, but more powerful as you can add multiple criteria.

4.  IF: =IF(Condition, Value If True, Value If False) 
This function is extremely valuable as a stand-alone or in conjunction with other “nested” functions. You can use cell references for the “Value If True” or the “Value If False”, or insert words (e.g. IF(A1>70, “Pass”, “Fail”). Just be sure to use quotation marks if inserting actual words. 

5.   IFERROR: =IFERROR(Value, Value If Error) 
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.

 No IFs about it, the IF functions are another great set of tools for Excel arsenal. Happy Holidays, All!