The SUMIF function

The =SUMIF($B3:$B12,$G3,D3:D12) formula in cell H3 of Figure 10.5 returns 149 as the sum of the miles for both instances of Ninety Mile Beach:

Figure 10.5 – The SUMIF function

The SUMIF function has three required arguments:

• Range – This refers to a row or column that you wish to search, which, in this case, is $B3:$B12.
• Criteria – This refers to a value to search for, which, in this case, is $G3.
• Sum_range – This refers to a row or column that you wish to add up values from, which, in this case, is D3:D12.

As shown in cell H8 of Figure 10.5, the =SUMIF($B3:$B12,$G8,D3:D12) formula returns 0 because the hyphenated Ninety-Mile Beach version does not appear in cells B3:B12. As we saw previously, in such situations, VLOOKUP and MATCH would return #N/A. Typically, you would not want to add both Ninety Mile Beaches together but would instead want to look up the length of one beach or the other...