Calculate SUMPRODUCT with multiple column criteria and one row criteria (with empty columns and text columns in the data range)

FITFLOP

2 min read

·

29-09-2024

31

0

Share

Mastering SUMPRODUCT with Multiple Column Criteria and One Row Criteria

Let's say you have a spreadsheet with data across multiple columns. You want to calculate the sum of values in a specific column, but only if certain criteria are met in other columns. This is where the SUMPRODUCT function shines, especially when dealing with complex conditions across multiple columns and rows.

Scenario:

Imagine a sales team's performance spreadsheet with columns for "Salesperson," "Region," "Product," "Quantity Sold," and "Revenue." You want to calculate the total revenue generated by all salespeople in the "North" region who sold "Product A" and "Product B".

Here's an example of the spreadsheet data:

The original code for this problem could look like this:

=SUMPRODUCT((A2:A7="John Smith")*(B2:B7="North")*(C2:C7="Product A")*(E2:E7))

This formula will calculate the sum of revenue generated by John Smith in the North region selling product A. However, this code only works if the 'Salesperson' and 'Region' are specified and not for multiple products. We need a better formula for our specific scenario.

The Solution:

To handle multiple products, we need to modify the formula to include all products in the criteria range. We can do this by using a combination of SUMPRODUCT, MATCH, and ISNUMBER functions.

=SUMPRODUCT((B2:B7="North")*(ISNUMBER(MATCH(C2:C7,{"Product A","Product B"},0)))*(E2:E7))

Explanation:

1. (B2:B7="North"): This part checks if the "Region" is "North".
2. (ISNUMBER(MATCH(C2:C7,{"Product A","Product B"},0))): This part checks if the "Product" is either "Product A" or "Product B".
   • MATCH(C2:C7,{"Product A","Product B"},0) searches for a match between each product in column C and the array {"Product A","Product B"}. It returns a number if a match is found.
   • ISNUMBER() checks if the MATCH function returned a number. If it did, the product matches one of the specified products.
3. (E2:E7): This is the column containing the "Revenue" that we want to sum.

How it Works:

• The formula iterates through each row in the data range.
• For each row, it checks if the "Region" is "North" and if the "Product" is either "Product A" or "Product B".
• If both conditions are met, it includes the corresponding "Revenue" in the final sum.

Additional Points:

• This formula can be easily adapted to handle different scenarios by changing the criteria arrays within the MATCH function.
• If you have multiple criteria in multiple columns, you can add more conditions to the formula using additional * operators.
• This formula is useful for complex calculations with dynamic data, particularly when dealing with conditions spanning multiple columns and rows.

Key Takeaways:

• SUMPRODUCT is a versatile function for calculating sums based on multiple criteria.
• Using MATCH and ISNUMBER allows you to efficiently check for multiple values within a specified range.
• By combining these functions, you can create powerful formulas to analyze complex data.

Resources:

• Microsoft Excel SUMPRODUCT Function
• Excel Formula Examples - SUMPRODUCT

With this enhanced understanding, you can confidently tackle complex calculations involving multiple criteria using the SUMPRODUCT function. Remember, practice makes perfect. Start experimenting with different scenarios and see how SUMPRODUCT can elevate your spreadsheet analysis!