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.
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).
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.
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.