Using SUMPRODUCT Function to Count Records using Multiple Conditions

1 Flares Filament.io 1 Flares ×

There are many ways to count records using multiple conditions in Excel. These can range from using the COUNTIFS function, PivotTables or by simply filtering the data. However neither 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 waiting to be discovered.

Using SUMPRODUCT to Count Records

In this example the SUMPRODUCT function is being used to count the number of orders in the list shown below.

List of orders formatted as a table

This list has been formatted as a table providing many benefits. One of the benefits is the ease of referencing the necessary columns within a formula (Find out more about tables in Excel).

To use the SUMPRODUCT function to count records we will enter it 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.

For example, the formula below will count all the orders by the salesperson Peacock (J9), and for the product Tofu (K9).

Counting records with the Sumproduct function

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

If we want to count all the orders by the salesperson Peacock (J9), and also the Salesperson Taylor (J10) we could enter the formula below. The Plus operator (+) has been used to create the OR logic between conditions.

Using OR logic in the Sumproduct function

 

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.

Watch the Video

2 thoughts on “Using SUMPRODUCT Function to Count Records using Multiple Conditions

  1. Pingback: Excel Badminton League Table

  2. I thank you for a very helpful training you provide me since I joined your class through Internet. Go on Please help me and other fellows of this world the Mighty God bless you abundantly

Leave a Reply

Your email address will not be published. Required fields are marked *