Three Functions in One: Using SUMIFS in Excel


With so many functions on offer in Excel, it can be quite a challenge knowing the easiest way to do something.  SUMIFS happens to be one of those formulas that does the work of three formulas--SUM, IF, and AND--all at once!  Here's how it works.

Suppose we had important information in two separate columns, like a first and last name, and a value in a third column that we needed to SUM depending on the full name.

Unlike SUMIF, the first term in SUMIFS is the range of values we may or may not be summing--that depends on which of our criteria matches.

After that first term, each pair of terms thereafter specifies a cell range and the criterion that those cells need to meet.  The criteria check supplies the IF, and the ability to check pair after pair after pair of checks works much like an AND function.  Only if ALL of the criteria are true in a row does the value get included in the sum.  In this example, we're checking for a first name of "Harry" AND a last name of "Smith."

Only two rows meet these criteria, and the sum of values in these rows is 11.

Need additional help with SUMIFS or other Excel formulas?  Reach out today via live chat, email, or phone for live support getting your issue fixed in mere minutes!