**Microsoft Excel **offers a variety of methods for counting multiple conditions. We may do this by filtering data using **PivotTable** or **the COUNTIF function**. Although, hardly any of those methods offer** the SUMPRODUCT function’s** level of adaptability. In this article, we will demonstrate some examples to use the **SUMPRODUCT **function for counting with multiple criteria in Excel.

**Download Practice Workbook**

You can download the workbook and practice with them.

**Introduction to Excel SUMPRODUCT Function**

One or more arrays can be sent as arguments to **SUMPRODUCT,** which multiplies the corresponding values of each array before returning the sum of the products.

**⇒ Syntax**

The syntax of the **SUMPRODUCT** function is:

**SUMPRODUCT(array1,[array2],[array3],…)**

**⇒ Argument**

*array1:*** **(**Required**) The first array of numbers.

** array2:** (

**Optional**) The second array of numbers.

** array3:** (

**Optional**) The third array of numbers.

**⇒ Return Value**

Returns the sum of the products of the corresponding values from all the arrays.

**3 Ideal Examples to Use SUMPRODUCT Function for Counting with Multiple Criteria in Excel**

The **SUMPRODUCT **function in Excel could be able to help you if you need to count a range depending on a variety of criteria, some of the criteria are dependent on the logical tests. Let’s look at the examples of using this function for counting with multiple criteria.

**1. Count Rows with Multiple Criteria Using Excel SUMPRODUCT Function**

Counting rows that satisfy several criteria by utilizing Excel’s **SUMPRODUCT** function, we need to follow the procedures down.

**STEPS:**

- Firstly, we have to set up the dataset. For instance, we have a dataset that contains some items, the assumed amount of sales money which we name the column planned for, and also the original amount of sales.

- Secondly, select the cell
**(G5)**where we want to determine how many**Shampoo**rows really sold more than was planned

`=SUMPRODUCT(($D$5:$D$12>$C$5:$C$12)*($B$5:$B$12=F5))`

- Then, press the
**Enter**key to obtain the desired outcome.

**🔎**** How Does the Formula Work?**

The values of each row’s columns**$D$5:$D$12>$C$5:$C$12:****D**and**C**are contrasted using this logical formula. A**TRUE**message appears if the**D**column’s value exceeds column**C**‘s value. If not, a**FALSE**will appear and the array values will be returned as follows:**FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE**.This logical statement determines if cell**$B$5:$B$12=F5:****F5**is present in the range**B5:B10**. As a consequence, you will receive the following result:**TRUE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE**.These two arrays are multiplied into a single array using the multiplication operation, which yields the following result:**($D$5:$D$12>$C$5:$C$12)*($B$5:$B$12=F5):****0;0;0;1;0;1;0;0**.This**SUMPRODUCT(($D$5:$D$12>$C$5:$C$12)*($B$5:$B$12=F5)):****SUMPRODUCT**totals the integers in the array and outputs the following result:**2**.

**Read More:** **[Solved] SUMPRODUCT with Multiple Criteria Not Working in Excel**

**2. Apply SUMPRODUCT for Counting Consumers Based on Multiple Criteria**

In this example, we will look at the multiple **OR **criteria with the **SUMPRODUCT **function. The plus sign (**+**) would be used if **OR** logic were necessary. For this, let’s follow the steps below.

**STEPS:**

- In the first place, we just need a dataset. Suppose, we have some employees, their departments, and the joining year. Now we need to determine how many people have the ‘
**Marketing**’ department or the joining year ‘**2010**’. Make sure that if a person has both the year 2010 and the department ‘**Marketing**’, count them as**1**.

- Next, choose the cell where the formula should go. Here, it is
**G7**.

`=SUMPRODUCT(--(((C5:C12=F5)+(D5:D12=G5))>0))`

- Then, to get the result you want, press the
**Enter**key.

**🔎**** How Does the Formula Work?**

This is for the comparison, if the condition is met it will return*C5:C12=F5:***TRUE**otherwise**FALSE**. The values of the array is:**TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE; FALSE**.The outcome will be as follows:*D5:D12=G5:***TRUE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;TRUE**.The array produces the outcome seen below:**(C5:C12=F5)+(D5:D12=G5):****2;0;1;1;0;2;1;1.**This results in the following:**SUMPRODUCT(–(((C5:C12=F5)+(D5:D12=G5))>0)):****6**.

**Read More: SUMPRODUCT Multiple Criteria in Excel(3 Approaches)**

**3. Count with Multiple Conditions Using SUMPRODUCT Function**

This example will show the counting with multiple conditions using the Excel **SUMPRODUCT** function. Applying **AND** logic between each condition are done using the multiplication operator (*****). Follow the instructions to use the **SUMPRODUCT** function for counting with multiple criteria.

**STEPS:**

- To begin with, create a dataset. And the dataset accommodates some dates, items, customers, and sales. Now, we need to count the number of orders if this fulfills the conditions.

- Select the cell
**(H6)**where the formula should go next.

`=SUMPRODUCT((C5:C12=H4)*(D5:D12=H5))`

- Finally, hit
**Enter**to get the result you want.

**🔎**** How Does the Formula Work?**

You’ll get the following outcome:**C5:C12=H4):****TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE;FALSE**.It produces the following outcome:**D5:D12=H5:****TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE**.The array generates the following outcome:**SUMPRODUCT((C5:C12=H4)*(D5:D12=H5)):****2**.

**Read More: SUMPRODUCT Function with Multiple Columns in Excel (4 Simple Ways)**

**Things to Keep in Mind**

- Although it uses arrays, the formula is non-array. To insert this function, you do not need to use
**Ctrl**+**Shift**+**Enter**. - Each array’s length must be the same. Excel will issue a
**#VALUE!**error otherwise. - The arrays used by the
**SUMPRODUCT**function typically include all integers. However,**SUMPRODUCT**will treat every cell in an array that has a text value rather than a number as**zero**. - You may put a ‘
**–**’ in front of an array to make it into an array of integers if it only includes**Boolean**values (**TRUE**and**FALSE**).**0**for a**FALSE**,**1**for**TRUE**.

**Conclusion**

The above examples will assist you to **Use SUMPRODUCT Function for Counting with Multiple Criteria in Excel**. Hope this will help you! Please let us know in the comment section if you have any questions, suggestions, or feedback. Or you can glance at our other articles in the **ExcelDemy.com** blog!

## Related Articles

**SUMPRODUCT Across Multiple Sheets in Excel (2 Methods)****SUMPRODUCT Multiple Criteria Same Column (3 Useful Ways)****SUMPRODUCT Multiple Criteria Lookup in Excel**