How to Use SUMIF with Multiple Ranges in Excel

Using SUMIF with multiple ranges in Excel is useful when you want to sum values based on specific criteria from different ranges. In order to use SUMIF with multiple ranges, you can use the SUMPRODUCT function along with SUMIF. Here's a step-by-step guide on how to use SUMIF with multiple ranges in Excel:

1. Organize your data in a way that is easy to read and understand. Make sure your data is in separate columns or rows to avoid confusion.
2. Identify the criteria that you want to use for the SUMIF function. This can be a specific value, a cell reference, or a range of values.
3. Use the SUMPRODUCT function along with SUMIF to sum the values from different ranges based on the specified criteria. The syntax for this combination is:

=SUMPRODUCT(SUMIF(range1, criteria, sum_range1), SUMIF(range2, criteria, sum_range2))

Where:

• range1 and range2 are the ranges that contain the criteria
• criteria is the condition you want to test against
• sum_range1 and sum_range2 are the ranges that contain the values to be summed

Example

Let's assume we have the following data in Excel:

We want to calculate the total sales for Product A by both John and Jane. Here's how to do it using SUMIF with multiple ranges:

1. Identify the criteria: In this case, the criteria is Product A.
2. Identify the ranges: The ranges containing the criteria are the "Product" and "Salesperson" columns, and the ranges containing the values to be summed are the "Sales" column.
3. Use the SUMPRODUCT function along with SUMIF:

=SUMPRODUCT(SUMIF(A2:A7, "A", C2:C7), SUMIF(B2:B7, "John", C2:C7))

4. Press Enter to get the result. The total sales for Product A by both John and Jane will be displayed in the cell.

In this example, the result should be 600 (100 + 500).