Google Sheets SUMIFS Function

What does it do?

The Google Sheets SUMIFS function adds numbers together in a range when matching evaluations are TRUE in multiple columns.  The evaluations can use the logical expressions i.e. GREATER THAN, LESS THAN, NOT EQUAL TO, EQUAL TO, GREATER THAN or EQUAL TO, LESS THAN or EQUAL TO and EQUAL TO <, >, <>, = for numerical criterion.  Use the asterisk * symbol and question mark ? for partial matching text strings.

Syntax Explained

Google Sheets SUMIFS function in the formula bar showing syntax.

Sum_range: This is the range of cells you want to sum up.

Criteria_range1: This is the range where you want to see if your matching Criterion1 appears in.

Criterion1: This is the text string, number or expression to search/test for in the Critera_range.

Criteria_range2 and above (optional): As per Criteria_range1

Criterion2 and above (optional): As per Criterion1


1. SUMIFS to match a text string in two columns

Example of a Google Sheets SUMIFS function matching two text string criteria in two columns.

Above there are 3 examples of different combinations of Criterion1 and 2 (see G3:H5).  Both MUST be found in order for the function to add them together.    In the case of the activate cell only when Chair and London appear on the same row ONLY.

Since SUMIFS uses identical logic to SUMIF, you can apply the same techniques for partial matches and the logical operators.

Pro Tip

I personally don’t use SUMIF anymore since SUMIFS and using one criteria_range and criteria does exactly the same as SUMIF.  The Sum_range is located in a different place in SUMIF vs. SUMIFS so I figured I might as well stick to one method.  Works for me, I like simple! 😊