The SUMIFS function

The =SUMIFS(D3:D12,$B$3:$B$12,$G13,$C$3:$C$12,$G12) formula in cell H13 of Figure 10.5 returns 55 as the distance of Ninety Mile Beach in New Zealand.

The SUMIFS function can sum data based on up to 127 criteria:

• Sum_range – This refers to a row or column that you wish to add up values from, which, in this case, is D3:D12.
• Criteria_range1 – This refers to a row or column that you wish to search, which, in this case, is $B$3:$B$12.
• Criteria – This refers to a value to search for, which, in this case, is $G13 for Ninety Mile Beach.
• Criteria_range2 – This refers to a row or column that you wish to search, which, in this case, is $C$3:$C$12.
• Criteria2 – This refers to a value to search for, which, in this case, is $G12 for New Zealand.

SUMIFS returns zero if a match cannot be found, as shown in cell H18 of Figure 10.5. Keep adding additional criteria ranges and criteria as needed when you need to add...