IF statement and some variatons – nested IF, CountIF and SumIF.
by David Goodmanson
IF a picture paints a thousand words, then how many words have 5 characters or more? The answer? Use the IF function. The IF function is a simple and easy-to-use function that can be widely applied in the design of your Excel Worksheets.
In addition to the IF function, we will look at two variations of it, they are CountIF and SumIF.
Before considering my initial conundrum, let’s look at the basic structure of the IF function. As with all Excel worksheet functions we need to start the formula with an equal sign, let’s say we are putting the IF function into cell B1. See figure 1.
Figure 1 – IF function structure
So, there is a logical test e.g. A1>A2, the test uses logical operators such as greater than >, less than <, equal to =, not equal to <>, greater than or equal to >=, and less than or equal to <=. Now let's apply this to the cells shown in figure 1. Enter the following formula into cell B1,
=IF(A1>A2, “Cell A1 is bigger”, “Cell A1 is smaller”)
NB. The alternate values are in quotes to tell Excel the values are text.
Figure 2 – Simple example
In our example cell A1 is smaller (10 < 15). What if cell A2 is changed to 10? See figure 2, it still says cell A1 is smaller! What's going on here? Has Excel gone mad?? No, Excel is actually saying, "cell A1 is not bigger than cell A2, so the logical test is false, now the only option I have is to declare that cell A1 has the false value specified, namely Cell A1 is smaller". OK, that is a pitfall with how I designed my IF function, not Excel.
Nested IF function
If I really want to have the distinction correct between all cells being compared, i.e. the cell is bigger, smaller or equal to, then I need to create a nested IF function. (or you could say “>=”, but then the text would still be wrong, because our statements are not allowing for the cells to be equal).
Figure 3 – Nested IF function formula
Note, with any worksheet function the equals sign is only required with the initial function statement, i.e. the 2nd IF in our nested formula does not need an equals sign.
You can use up to 7 levels of nesting in a formula, (not that you really would but it is there).
In practice, the Nested IF function is used where there are 3 or more possibilities – usually two obvious choices and one slightly hidden. Examples of these are, (“Yes”, “No”, “Undecided”), (“Over Budget”, “Under Budget”, “On Budget”). Where just an “=” sign is used, something is either equal or it isn’t. Hence an equals sign, (or >=, or <=) removes the ambiguity that is possible in a Simple IF function.
So from figure 3 the formula is
=IF(A1>A2,”A1 is bigger”,IF(A1
here’s how it works:
Excel evaluates the logical test (A1>A2) and finds this to be false, so it skips the “condition is true” statement i.e. “A1 is bigger”, and proceeds to the “condition is false” argument. There it finds a second IF function which has a second logical test (A1
For further study of the IF function and Nested functions the Help in Excel has some excellent examples on this. In the help search box (up the top right), try “If” as one search and “Nested function” as another search. It is always worthwhile checking Help out during this series of articles, some are helpful, some are less than helpful. Take the good with the bad. I will try to offer advice of when (I think) they are helpful.
CountIf and SumIf
Finally, to my initial conundrum. Let us say we have 10 words (I know I said a thousand but, hey, that’s too many for this space), as shown below.
Figure 4 – Countif and Sumif functions
In figure 4 column A lists 10 words we will analyse (A2:A11). Column B uses a function called LEN() which counts the number of characters in a string of text. So, for example, the formula =LEN(A2) gives 5, meaning there are 5 characters in the word “named”. Applying this formula to our list as shown in column B, we derive the length of each word. Now, instead of using an IF function we can use the Countif function to count only those cells where the length is 5 characters or more. Similarly for SumIf, which will add the lengths
The result is shown in figure 4 cell E2 and has the formula,
=COUNTIF(B2:B11, “>=5”)
With this function the syntax is a bit different to the IF syntax. COUNTIF consists of a range of data B2:B11, and a criteria such as “>=5”. It is like a search function, we give it a range of cells to search and if the cell meets the criteria the function counts the cell or sums it. As simple as that. So in our word puzzle there are 8 cells where the character count is 5 or more, and if you added those counts together you’d get 54 characters. A little trivial I agree, but I’m sure you it illustrates the basic point and operation of these very handy functions.
I intend to explore these logical functions further in our next edition, so we can apply them in a wider range of applications. Take care until then.
