What does it do?
The Excel 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
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 Criteria1 appears in.
Criteria1: This is the text string, number or expression to search/test for in the Critera_range.
Criteria_range2 to 127 (optional): As per Criteria_range1
Criteria2 to 127 (optional): As per Criteria1
Examples
1. SUMIFS to match a text string in two columns
Above there are 3 examples of different combinations of Criteria1 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! 😊