In this lesson, I’ll demonstrate 5 effective approaches on how to use the **SUMPRODUCT** function for multiple criteria in the same column including some other problems related to this one. These techniques will enable you to solve the problem that led you here. You will master several crucial Excel features and functions during this article, which will come in extremely handy for any assignment using Excel.

## 📁 Download Excel File

The sample worksheet that was used during the discussion is available to download for free right here.

## Learn to Use SUMPRODUCT for Multiple Criteria in the Same Column with These 5 Methods

Say we have a dataset of Some store that has different data similar to our problem. We will use **SUMPRODUCT **function to determine these problems as it can be used in different ways with the combination of logics. Follow these approaches the learn **SUMPRODUCT **functions application for multiple criteria along same column.

**Method 1**

### 1. SUMPRODUCT with Multiple Criteria Comparing Two or More Arrays

**SUMPRODUCT** is a versatile function. We can do different operations with this function. For instance we have a dataset of different IDs product sale lists against their respective targets. We are going to determine **How many IDs couldn’t meet their targets**.

⬇️⬇️ **STEPS **⬇️⬇️

- First,
**select**the cell to put in the formula. - After that copy down the following formula or modify it as per your problem.

**=SUMPRODUCT((E6:E14<D6:D14)*1)**

- Therefore, press
**Enter**for the result.

- In addition, if you want specific answers like how many IDs selling
**Mouse**couldn’t meet their targets try using the following code.

**=SUMPRODUCT((E6:E14<D6:D14)*(C6:C14=”Mouse”))**

- Press
**Enter**for the result.

**Method 2**

### 2. Application of OR Logic

Utilize plus sign (+) among the arrays to selectively add up or count cells using the OR algorithm. We can use this with **SUMPRODUCT**. Let’s go through a problem. For instance, let’s say we have the same dataset as above and we want to know the total sale for Mouse and Monitor. Follow the steps below.

⬇️⬇️ **STEPS **⬇️⬇️

- First,
**select**a cell and copy down the following formula for the**count**of the products.

**=SUMPRODUCT((C6:C14=”Mouse”)+(C6:C14=”Monitor”)) **

- Press
**Enter**and copy the following formula for determining the Total sale amount of those two products.

**=SUMPRODUCT((C6:C14=”Mouse”)+(C6:C14=”Monitor”), E6:E14)**

- Now again pressing
**Enter**we can see the result.

** **

**Method 3**

### 3. Using AND Logic in SUMPRODUCT Function

We can use the **SUMPRODUCT** function and employ AND algorithm to solve certain problems. Let’s say we have a data set like the previous problem. Now we will try to determine two specific criteria. We will find how many sales ID **G** acquired by selling **Mouse**.

⬇️⬇️ **STEPS** ⬇️⬇️

- First,
**select**the cell and apply the following formula.

**=SUMPRODUCT((B6:B14=”G”)*(C6:C14=”Mouse”)*E6:E14)**

- After that press
**Enter**to view the result.

**Method 4**

### 4. Using Both AND and OR Logic

The AND operator is represented by an asterisk (*) and the OR operator is represented by the plus sign (+). We will use both the logic using the **SUMPRODUCT **function to determine the total sale of **Keyboards** and **Mouse** from **Razer** company. See the instructions below.

⬇️⬇️ **STEPS** ⬇️⬇️

- First,
**select**a cell for the input and copy the following formula or modify it as you need.

**=SUMPRODUCT((B6:B14=”Razer”)*((C6:C14=”Keyboard”)+(C6:C14=”Mouse”))*D6:D14)**

- Now Press
**Enter**for results.

**Method 5**

### 5. SUMPRODUCT Formula for Weighted Average

Excel’s **SUMPRODUCT** is frequently used to compute weighted averages, wherein every number is given a specific weight. Here we will be demonstrating this.

⬇️⬇️ **STEPS** ⬇️⬇️

- First,
**choose**a cell and copy the following formula.

**=SUMPRODUCT(C6:C14,D6:D14)/SUM(D6:D14)**

🔨** Formula Breakdown**

**👉 SUMPRODUCT(C6:C14,D6:D14)**

This part selects **C6:C14** which are the values of column** C** and multiplies it with the data from column **D** which are **D6:D14 **then it **SUMs** them up.

👉 **SUM(D6:D14)**

This portion of the formula sums the total column **D6:D14** which for our case would be **37**.

👉 **SUMPRODUCT(C6:C14,D6:D14)/SUM(D6:D14)**

This will **divide** the **SUMPRODUCT** of column **C** and **D** that means the multiplied summation, **by** the **SUM **of Column **D, **which gives us the **Weighted Average**.

- This formula will determine the weighted average of our data. So press
**Enter**for the result.

## 📄 Important Notes

`🖊️`

Be careful while using the code as there are a lot of terms and if you lose concentration the code may be incorrect.

`🖊️`

Use shortcuts to reduce time.

`🖊️`

Change the** formats **as you want them before applying a formula.

`🖊️`

While writing formulas carefully see the suggestions and don’t forget the commas and parentheses.

## 📝 Takeaways from This Article

`📌`

Use of the **SUMPRODUCT **function.

`📌`

Different approaches can be used using just one function.

`📌`

How to use **AND **as well as **OR **logic in a function using * and + respectively.

**Conclusion**

I hope you were able to use the techniques I demonstrated in this Excel lesson to use the **SUMPRODUCT** function for multiple criteria in the same column. As you can see, there are a lot of options on how to do this. Decide deliberately on the approach that best addresses your circumstance. I advise repeating the steps if you become confused in any of the steps if you get stuck. Practice on your own after taking a look at the **Sumproduct Multiple Criteria Same Column** Excel file in the practice workbook as I’ve provided above because practice makes a man perfect. I earnestly hope you can use it. Please share your experience with us in the comment box about how your experience was throughout the article and what we need to improve or add to make things better for you folks.

Again if you have any problem regarding Excel spit it in the comment box. The **Excelden **crew is always available to answer your questions. Keep educating yourself by reading. For more Excel-related articles please visit our website **Excelden.com**.