SUMPRODUCT with Criteria

This tutorial assumes you understand the basic functionality of SUMPRODUCT – in that it finds the product of corresponding values in each column/row/array and then sums those calculation results. We will focus on the opportunities to count and sum records based on criteria using SUMPRODUCT.

Using SUMPRODUCT to Count with Criteria

In our first scenario we have been asked to count the number of accounts that are over budget, in other words where the actual figure is greater than the budget figure.  Accounts are shown below…

Accounts Data

A simple logical test would ascertain if an account is over budget eg. =E2>D2.  Excel will return TRUE if it is and FALSE if it isn’t, as shown below.

Showing TRUE and FALSE

If we convert TRUE results to equal 1 and FALSE results to equal 0 then we can add up the results and find the number of over budget accounts.  There are number of ways we could do this, but a popular method is to use a double unary operator ( – – ).

See the results below…

Totalling over budget accounts

So we have achieved the result without using the SUMPRODUCT function. The benefit of using SUMPRODUCT however, is that it does away with the need to create a helper column.  A helper column is where you perform the intermediary step in your calculation – in our scenario this is the OVER BUDGET? column.  With SUMPRODUCT we can go straight to the answer.  SUMPRODUCT allows you to perform calculations on arrays, in other words we can work out all the 0’s and 1’s in one step rather than with lots of individual formulas.

Here’s how the formula would look…

A useful technique to help you understand how this type of array formula works, is to select the relevant part of the formula and press the F9 key on your keyboard.  For example, selecting this part of the formula and using F9…

SUMPRODUCT formula

…results in this…

SUMPRODUCT formula partially evaluated

F9 lets you temporarily evaluate a portion of your formula.

You will note that the positions of the 1s in the formula correspond to the position of the 1s in the OVER BUDGET? helper column.  By the way always undo after you have used the F9 trick or use ESC, or you will permanently hard code the array results.

Using SUMPRODUCT to Sum with Criteria

What if we wanted to calculate the total amount over budget? One way of doing this would be to create two additional helper columns, one which calculated the difference between the BUDGET and the ACTUAL figures and the other which multiplied this difference by the value in the OVER BUDGET? column.  See the image below which shows the formulas in these new helper columns.

Helper columns showing formula

In the TOTAL OVER BUDGET column we can then add up the over budget amounts. The results look like this…

Helper column showing results

With SUMPRODUCT we can perform this calculation without the need of any helper columns.  The formula we used to count the number of over budget accounts is our starting point. If you remember it looked like this…

SUMPRODUCT formula

To get it to sum up the over budget accounts we can use the array2 argument to perform the variance calculation as shown below…

SUMPRODUCT formula adding over budget accounts

To help you understand how this works, I am going to use the F9 trick to display the results of the two arrays…

This is the calculation SUMPRODUCT performs…

The calculation the SUMPRODUCT formula performs

SUMPRODUCT vs COUNTIF and SUMIF

These SUMPRODUCT calculations are similar to COUNTIF and SUMIF: COUNTIF counts based on a criteria, SUMIF sums up based on a criteria. However both of these functions, including their plural counterparts would still require helper columns in our scenario.  COUNTIF is incapable of performing the OVERBUDGET? test and SUMIF is incapable of performing the VARIANCE calculation.

SUMPRODUCT vs Array Formula

An array formula does provide a working alternative to SUMPRODUCT.  Counting the number of accounts over budget would use a formula like this…

Summing up the over budget accounts would use this formula…

Array formulas require CTRL SHIFT ENTER to confirm entry and after each edit of the formula. SUMPRODUCT therefore provides an easier and more convenient solution.

Posted by Chester Tugwell