SUMPRODUCT Function to Count with Multiple Conditions

There are many ways to count with multiple conditions in Excel. These can range from using the COUNTIFS function, PivotTables, or by simply filtering the data. However, none of them are as flexible as the SUMPRODUCT function.

The SUMPRODUCT function is used to multiply the values of multiple arrays and then sum the totals. However, it is capable of so much more amazing feats. It truly is one of those hidden gems in Excel waiting to be discovered.

Using SUMPRODUCT to Count with Multiple Conditions

In this example, the SUMPRODUCT function is being used to count the number of sales based on multiple criteria. The following image, shows a snapshot of a sales list. The list has been formatted as a table named tblSales.

To use the SUMPRODUCT function to count records, we will enter the formula as below.

=SUMPRODUCT((condition 1)*(condition 2)*(condition 3))

Each condition, or array, is enclosed within its own set of brackets. The multiplication operator (*) is used to apply the AND logic between each condition.

If we required OR logic, the plus operator (+) would be used. We will see this shortly, but let’s begin with AND logic.

The following formula counts all the sales for the product entered in cell B3, and for the customer in cell C3.

=SUMPRODUCT((tblSales[Product]=B3)*(tblSales[Customer]=C3))

An area where SUMPRODUCT demonstrates its flexibility over alternatives such as COUNTIFS, is its ability to handle OR logic between conditions.

The following formula is used to count all the sales for the products in cell B3 and B4. The plus operator (+) has been used to create the OR logic between conditions. Notice each condition is within its own set of brackets again.

=SUMPRODUCT((tblSales[Product]=B3)+(tblSales[Product]=B4))

The SUMPRODUCT function can handle many more conditions if necessary. When you get to grips with it, you will feel like no data analysis is out of your reach.