Sum if with Multiple Ranges in Excel

To sum values across multiple ranges based on a condition, use the SUMIF function for a single range, or the SUMIFS function for multiple criteria and ranges. For summing with multiple ranges that share the same criteria, you can combine multiple SUMIF functions or use SUMPRODUCT for more complex logic. This article explains the methods, provides examples, and addresses common pitfalls.

Formula

The core formula for summing with multiple ranges depends on the scenario:

• SUMIF(range, criteria, [sum_range]) – sums values in a single range that meet a single condition.
• SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …) – sums values that meet multiple conditions across multiple ranges.
• SUMPRODUCT((range1=criteria1)*(range2=criteria2)*sum_range) – for complex logic or when ranges are not contiguous.

In the calculator above, the formula uses a simplified representation: it sums all values in the list that are greater than 50. The actual Excel formula would be =SUMIF(A2:A10, ">50") if the values are in A2:A10.

Worked Example

Using the default values from the calculator: Value = 100, Condition = ">50". The calculator sums all numbers greater than 50 in the list. Since the list contains only 100, the result is 100.

In Excel, suppose you have sales data in column B (amounts) and region in column A. To sum all sales over $500 in the "East" region, you would use:

=SUMIFS(B2:B100, A2:A100, "East", B2:B100, ">500")

This sums all values in B2:B100 where the corresponding A cell is "East" and the B cell is greater than 500.

How to Use SUMIF and SUMIFS

SUMIF is used when you have one condition. For example, sum all sales from the "West" region: =SUMIF(A2:A100, "West", B2:B100). SUMIFS is for multiple conditions, and it always places the sum range first, unlike SUMIF.

Key differences:

• SUMIF(range, criteria, [sum_range]) – sum_range is optional; if omitted, it sums the range itself.
• SUMIFS(sum_range, criteria_range1, criteria1, …) – sum_range is required and comes first.
• Both support wildcards (* and ?) for partial matches.

Summing Across Multiple Ranges with the Same Criteria

If you need to sum values from multiple non-contiguous ranges that share a common condition, you can add multiple SUMIF functions together. For example, to sum values in ranges A1:A10 and C1:C10 where the corresponding B and D cells are "Yes":

=SUMIF(B1:B10, "Yes", A1:A10) + SUMIF(D1:D10, "Yes", C1:C10)

Alternatively, use SUMPRODUCT to handle arrays:

=SUMPRODUCT((B1:B10="Yes")*A1:A10 + (D1:D10="Yes")*C1:C10)

The SUMPRODUCT approach is more flexible for complex logic but can be slower on large datasets.

Common Mistakes and How to Avoid Them

One frequent error is mixing up the order of arguments in SUMIFS. Remember: SUMIFS(sum_range, criteria_range1, criteria1, …). Another mistake is using a criteria range that is a different size than the sum range, which causes #VALUE! errors.

Also, criteria text must be enclosed in quotes, and numeric criteria with operators (like ">50") must be in quotes as well. For cell references, use concatenation: ">"&D1.

When using wildcards, remember that * matches any sequence, ? matches a single character, and ~ escapes wildcards. For example, to match cells starting with "A", use "A*".

When to Use SUMPRODUCT Instead

SUMPRODUCT is useful when you need to sum with multiple conditions across multiple ranges without adding multiple SUMIFS. It can also handle OR logic (using + instead of *) and is more flexible for array operations. However, it is less intuitive and can be slower on large datasets.

Example: Sum sales where region is "East" OR "West":

=SUMPRODUCT((A2:A100="East")+(A2:A100="West"), B2:B100)

Note that using + in SUMPRODUCT can double-count if both conditions are true, but for mutually exclusive categories it’s fine.

Limitations and Performance Considerations

SUMIFS and SUMIF are optimized for speed, but they cannot handle arrays that are not contiguous. If your data is in a table, consider using structured references (e.g., Table1[Amount]) for better readability and automatic range expansion.

For large datasets (over 100,000 rows), SUMPRODUCT can slow down calculations. In such cases, consider using a pivot table or Power Query for aggregation.

Also note that SUMIFS is case-insensitive. If you need case-sensitive matching, you must use SUMPRODUCT with EXACT.

Frequently Asked Questions

Can I use SUMIF with multiple criteria ranges?

No, SUMIF only accepts one criteria range. For multiple criteria, use SUMIFS. If you need to sum with multiple ranges and criteria, combine multiple SUMIFS or use SUMPRODUCT.

Why is my SUMIFS returning 0?

Check that your criteria are spelled correctly and that the criteria ranges are the same size as the sum range. Also, ensure that numeric criteria are not stored as text. Use the TRIM function to remove extra spaces.

How do I sum based on a condition in another column?

Use SUMIFS with the sum range as the column you want to sum, and the criteria range as the column with the condition. For example, =SUMIFS(C2:C100, A2:A100, "Yes") sums column C where column A is "Yes".

Can I use SUMIF with dates?

Yes, but dates must be entered as date values or using the DATE function. For example, =SUMIF(A2:A100, ">"&DATE(2023,1,1), B2:B100) sums all values after January 1, 2023.

What is the difference between SUMIF and SUMIFS?

SUMIF sums based on one condition and has an optional sum_range. SUMIFS sums based on multiple conditions and requires the sum_range first. SUMIFS is the more versatile function and is recommended for most use cases.

How do I sum cells based on cell color?

SUMIF and SUMIFS cannot sum by color. You would need to use VBA or the GET.CELL function in a named range, which is advanced. Alternatively, add a helper column with a formula that identifies the color and then use SUMIF on that helper column.

Related

• All Ride a Pet Eggs? Meaning & Origin
• Single Track Roads Scotland: Passing Places Etiquette
• All Stand Skins in Bizarre Lineage?

in val | Value to sum | - | 100 | 0
in cond | Condition (e.g., >50) | - | >50 | 0
out result | Sum of values meeting condition | - | sum(if(val>50,val,0)) | 2