SUMPRODUCT With Multiple Criteria In Google Sheets

What Is SUMPRODUCT With Multiple Criteria In Google Sheets?

SUMPRODUCT with multiple criteria in Google sheets is a function grouped under SUMPRODUCT function. It helps users find the result of data with multiple criteria or conditions. It is a default function in Excel and Google sheets. Users can use this function to calculate sum or total with multiple criteria.

For example, consider the below table showing product, quality, price and quantity in columns A, B, C and D, respectively.

Now, let us learn how to use SUMPRODUCT function in Google sheets to find the result of item, ‘Mango’ and quality – ‘C’.

To begin with, insert the SUMPRODUCT formula in C12. The complete formula is =SUMPRODUCT(–($A$2:$A$9=A12),–($B$2:$B$9=B12),$C$2:$C$9,$D$2:$D$9) and press Enter key.

We will be able to see the result in cell C12 as shown in the below image.

Likewise, we can find the result using SUMPRODUCT function multiple criteria.

In this article, let us learn how to use the SUMPRODUCT function in Google sheets with multiple criteria with detailed examples.

SUMPRODUCT With Multiple Criteria() In Google Sheets Formula

The formula of SUMPRODUCT function with multiple criteria in Google sheets is =SUMPRODUCT(array1,[arrray2],[array3],…)

where

1. Array1 is the first array which we want to multiply and then add. It is the only mandatory argument.
2. Array2,array3 are all optional arguments which we want to include in the formula.

Now, the formula which we want to use while using SUMPRODUCT with multiple criteria is

=SUMPRODUCT(–(array1criteria),–(array2criteria)…,arrays)

Alternatively, we can also use the following formula.

=SUMPRODUCT((array1criteria)*(array2criteria)…*arrays)

where

• Array shows the data which we want to add
• Criteria shows the condition with which we want to find the SUMPRODUCT

All the arguments shown in the two formulas are mandatory.

How To Use SUMPRODUCT With SUMPRODUCT With Multiple Criteria In Google Sheets Function?

We can use the SUMPRODUCT function in Google sheets with multiple criteria using the below steps.

They are:

The steps are:

Step 1: To begin with, we need to insert the cell range in the spreadsheet. Next, we need to select the cell where we want to find the result.

Step 2: Next, insert the SUMPRODUCT function with the formula.

Step 3: Press Enter key.

We will be able to see the result in the active cell as shown in the below image.

Likewise, we can find the result using SUMPRODUCT function multiple criteria.

Examples

Let us learn how to use SUMPRODUCT function with multiple criterions with the following examples.

Example #1 – Sales Performance Analysis By Region And Product Category

For example, consider the below table showing name, region, budget and projects in columns A, B, C and D, respectively.

Now, let us learn how to use SUMPRODUCT function in Google sheets to find the result of sales performance analysis by region and product category, which is ‘George’ and region ‘North’.

The steps are:

Step 1: To begin with, we need to insert the cell range in the spreadsheet. In this example, the data is available in the cell range A1:D9. Next, we need to select the cell where we want to find the result. In this example, we need to select the cell C12.

Step 2: Next, insert the SUMPRODUCT function with the formula.

So, the complete formula is =SUMPRODUCT(–($A$2:$A$9=A12),–($B$2:$B$9=B12),$C$2:$C$9,$D$2:$D$9)

Step 3: Press Enter key.

We will be able to see the result in cell C12 as shown in the below image.

Likewise, we can find the result using SUMPRODUCT function multiple criteria.

Example #2 – Performance Evaluation Based On Multiple Criteria

For example, consider the below table showing name, appraisal, appraisal amount and projects in columns A, B, C and D, respectively.

Now, let us learn how to use SUMPRODUCT function in Google sheets to find the result of sales performance evaluation based on multiple criteria, which is ‘Cindy’ and appraisal ‘A-’.

The steps are:

Step 1: To begin with, we need to insert the cell range in the spreadsheet. In this example, the data is available in the cell range A1:D9. Next, we need to select the cell where we want to find the result. In this example, we need to select the cell C12.

Step 2: Next, insert the SUMPRODUCT function with the formula.

So, the complete formula is =SUMPRODUCT(–($A$2:$A$9=A12),–($B$2:$B$9=B12),$C$2:$C$9,$D$2:$D$9)

Step 3: Press Enter key.

We will be able to see the result in cell C12 as shown in the below image.

Likewise, we can find the result using SUMPRODUCT function multiple criteria.

Example #3 – Inventory Analysis By Product Type And Location

For example, consider the below table showing product, location, price and quality in columns A, B, C and D, respectively.

Now, let us learn how to use SUMPRODUCT function in Google sheets to find the result of inventory analysis by product type and location, which is ‘Fig’ and location ‘South’.

The steps are:

Step 1: To begin with, we need to insert the cell range in the spreadsheet. In this example, the data is available in the cell range A1:D9. Next, we need to select the cell where we want to find the result. In this example, we need to select the cell C12.

Step 2: Next, insert the SUMPRODUCT function with the formula.

So, the complete formula is =SUMPRODUCT(–($A$2:$A$9=A12),–($B$2:$B$9=B12),$C$2:$C$9,$D$2:$D$9)

Step 3: Press Enter key.

We will be able to see the result in cell C12 as shown in the below image.

Likewise, we can find the result using SUMPRODUCT function multiple criteria.

Example #4 – Attendance Tracking For Specific Event And Date

For example, consider the below table showing student, grade, marks and attendance in columns A, B, C and D, respectively.

Now, let us learn how to use SUMPRODUCT function in Google sheets to find the result of attendance tracking for specific event and date, which is ‘Jill’ and grade ‘C’.

The steps are:

Step 1: To begin with, we need to insert the cell range in the spreadsheet. In this example, the data is available in the cell range A1:D9. Next, we need to select the cell where we want to find the result. In this example, we need to select the cell C12.

Step 2: Next, insert the SUMPRODUCT function with the formula.

So, the complete formula is =SUMPRODUCT(–($A$2:$A$9=A12),–($B$2:$B$9=B12),$C$2:$C$9,$D$2:$D$9)

Step 3: Press Enter key.

We will be able to see the result in cell C12 as shown in the below image.

Likewise, we can find the result using SUMPRODUCT function multiple criteria.

Important Things To Note

1. SUMPRODUCT function in Google sheets is a function used to find the total after multiplying.
2. While working with huge datasets, we need to make sure to use SUNPRODUCT function with multiple criteria if there are more than one condition or criteria.
3. Remember, to include the number of array and criterias correctly to avoid errors.

Frequently Asked Questions (FAQs)

Download Template

This article must be helpful to understand the SUMPRODUCT With Multiple Criteria In Google Sheets, with its formula and examples. You can download the template here to use it instantly.

Recommended Articles

Guide to What Is SUMPRODUCT With Multiple Criteria In Google Sheets. Here we learn how to use SUMPRODUCT With Multiple Criteria In Google Sheets with examples and points to remember. You can learn more from the following articles.

Combine Text From Two Or More Cells Into One Cell In Google Sheets

Bullet Points In Google Sheets

Freeze Cells In Google Sheets