The SUMPRODUCT function is one of the oldest in MS Excel, and most of the key functionalities it was well known for have been overtaken by newer functions such as COUNTIF and SUMIF. However, there are a couple of interesting ways the SUMPRODUCT function could come in handy to handle some common tasks in MS Excel. This post shows five of those ways.

First things first, how does the SUMPRODUCT function work? The SUMPRODUCT is a function that returns the products of corresponding ranges or arrays. It is essentially an array formula, and unlike most other functions that apply a cell to a cell, or a cell to an array, it can compute multiple arrays against one another. The modality of how =SUMPRODUCT functions work is described below:

• Compute total sales: SUMPRODUCT() can be used to compute total sales (product volume sold X price of the product) in a performance dataset…

• Calculate weighted average: If you are conducting a vendor evaluation or assessing a customer survey, or any other activity to arrive at a composite final score, some assessment points may have higher weightings than others.

• Count the number of distinct values in a range: To count the number of unique items in a range using SUMPRODUCT, the syntax for the formula is as follows:

=SUMPRODUCT(1/COUNTIF([range],[range]))

You can also use =SUM to count the unique items in a range – click here for that hack.

• Count how many products (or regions, customers, etc) met their target: SUMPRODUCT can help you quickly analyze an actuals vs target performance dataset to pinpoint numbers that met, didn’t meet or exceeded their targets. And this is done through its power to analyse multiple sets of arrays on each other simultaneously.

This kind of application can be done for any other scenario where you need to summarize how many times the item in column A is equal to column B, or vice versa.

• Count how many strings in a range start with a given prefix, or how many numbers in a range start with the same order of numeric characters: I show this in more detail in Excel Hack 57.