< Go Back

SUMIF with Multiple Criteria

SUMIF function is designed to sum numbers based on one criterion. By default it considers one range, one criteria and one sum_range as per its syntax. But if you want to add numbers based on multiple criteria then you have following options to use.

  • You add up multiple SUMIF functions based on OR logic, applied for each criterion separately.
  • You need to use SUMIFS function that is by default designed to sum numbers with multiple criteria, based on AND logic .
  • You can also use SUMIFS function to sum number with multiple criteria, based on OR logic, with an array constant.

In this article you will learn how to sum numbers based on multiple criteria by using three of these options. Let’s assume you have data set of sales orders for various products, and you want to sum order amounts with multiple criteria.

SUMIF function with multiple criteria based on OR logic

If you want to add numbers that meet either of the criteria (OR logic) from multiple criteria then you need to sum up two or more SUMIF functions in single formula. Suppose you want to sum order amounts for “Beans” and “Broccoli” products using OR logic then you need to sum up two SUMIF functions in single formula using following pattern;

=SUMIF(range, criteria1, sum_range) + SUMIF(range, criteria2, sum_range)

=SUMIF(B2:B22,"Beans",D2:D22)+SUMIF(B2:B22,"Broccoli",D2:D22)

OR

=SUMIF(B2:B22,G2,D2:D22)+SUMIF(B2:B22,H2,D2:D22)

SUMIFS function with multiple criteria based on AND logic

If you want to sum numbers from a range when all of the specified criteria are met, based on AND logic, then you need to use SUMIFS function. It is important to know that all of criteria must be met on single or multiple ranges to sum up numbers from sum_range.

Syntax of SUMIFS is;

SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2,...)

Suppose, you want to sum the orders’ amounts that are delivered between two dates then you will use SUMIFS function. Here you need to supply two criteria on same range and where both of these criteria are met SUMIFS function sums those orders’ amounts.

=SUMIFS(D2:D22,E2:E22,">="&G2,E2:E22,"<="&H2)

Here, you have supplied two dates as criteria in cell references G2 and H2 along with expression “>=” and “<=”.

SUMIFS function with multiple criteria based on OR logic

As SUMIFS function by default entertain multiple criteria based on AND logic, but to sum numbers based on multiple criteria using OR logic, you need to SUMIFS function within an array constant.

An array constant is a set of multiple criteria provided in curly braces {} in formula, like

{“criteria1”,”criteria2”,”criteria3”}

Array constant using OR logic forces SUMIFS function to sum numbers based on either of the multiple criteria in an array result and finally SUMfunction add up those array results, like;

=SUM(SUMIFS(sum_range, criteria_range , {“criteria1”,”criteria2”,”criteria3”}))

=SUM({value1, value2, value3})

Suppose you want to sum orders’ amounts for either of the products “Orange” and “Apple” supplied as criteria in array constant then you need to provide multiple criteria in SUMIFS function as follows;

=SUM(SUMIFS(D2:D22,B2:B22,{"Orange","Apple"}))

Remember, you cannot use expression or cell reference in array constant.

Here, array constant forces SUMIFS function to generate result in an array, like followings;

=SUM ({5866, 17955})

=23,821

Finally, SUM function adds up these array results to come up with total figure as shown below;

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar