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...
