Step 23 of 40 - COUNTIFS Function

The final formula in cell M2 should look like this:

=IF(F2="","",IF(COUNTIF($G$1:G2,G2)=1,G2,G2+RAND()))

The IF function has three arguments:

• logical_test - a calculation that evaluates to TRUE or FALSE

• value_if_true - an optional argument that will return an amount or calculation if the logical_test returns TRUE

• value_if_false - an optional argument that will reutrn an amount or calculation if the logical_test returns FALSE

Since value_if_false is optional, Excel will accept the formula if you leave that argument out, which can inadvertently happen if you have a misplaced comma or parenthesis. If your formula is mistyped but Excel accepts it, the formula will return FALSE on certain rows instead of the value you expected.

The formula in cell M2 is nesting two IF functions together. You can nest up to 64 IF functions in this fashion, although in general if you find yourself nesting more than three or four levels deep there is typically a better way to write the formula you're attempting. It becomes exponentially harder to test all of the outcomes as you nest ever more IF functions together.

Regardless, here's the formula again: =IF(F2="","",IF(COUNTIF($G$1:G2,G2)=1,G2,G2+RAND()))

The first logical_test is F2="", which checks to see if cell F2 is blank. If so, the value_if_true is "", which makes the cell appear blank, otherwise Excel will use the value_if_false, which is another IF function. You can nest up to 64 IF functions in this fashion. The value_if_true for the second IF function is COUNTIF($G$1:G2,G2)=1, which is checking to see if this the first time this amount appears in column G. If the logical_test returns TRUE then the value_if_true returns the value in cell G2, otherwise the value_if_false argument returns the value of G2+RAND(). The RAND function returns a random number between 0 and 1, and so adding this to the value in G2 ensures a unique amount on every row, which you'll then use later in the assignment.