Classic SUMPRODUCT

The conventional use of the SUMPRODUCT function can remove the need for performing intermediary row calculations, therefore reducing calculation time and improving spreadsheet performance.

In Figure 9-1, there is some transactional data, and formulas have been entered in column D to multiply the price by the number sold. These row totals are then summed in cell D8 with a simple SUM function.

=SUM(D2:D6)

This is fine, and the row totals may be useful. However, if there were thousands of rows in this dataset, this may be an unnecessary volume of calculations and strain on the spreadsheet.

SUMPRODUCT multiplies the values in the corresponding arrays of B2:B6 and C2:C6, for example, 48*8, 53*2, 37*12. These intermediary totals are then summed to produce the result 1236.

Only two arrays were used in this example, and SUMPRODUCT can handle up to 255 arrays . Let’s move on and see further examples of the extreme power and versatility of this function.

Sum Values That Meet Multiple Criteria

The SUMPRODUCT function is fantastic for summing and counting values that meet multiple criteria. It can handle both AND and OR logic, so the criteria can be complex.

It is much more efficient at handling different logical tests than the IFS family of functions (SUMIFS, COUNTIFS, etc.). We saw a technique to use OR logic with the SUMIFS function in the previous chapter, as natively it cannot apply OR logic.

Let’s start by using the SUMPRODUCT function to sum values that meet a single condition. We will then increase the complexity of the criteria by adding more conditions and explain how it all works.

For these examples, we will use the data in the table named [tblSales] on the [Data] worksheet. The first few rows of this table are shown in Figure 9-3.

In Figure 9-4, the following formula sums the [Total] values for all sales of the product entered in cell B3:

=SUMPRODUCT((tblSales[Product Name]=B3)*tblSales[Total])

This formula is quite different to others that we have seen so far in this book. Two things that stand out are that the condition is enclosed within its own set of brackets and that the results of that condition are multiplied by the values in the [Total] column.

Let’s break down how this all works.

Now that we understand how this is working, let’s add a second condition to our SUMPRODUCT formula.

In this formula, each condition is enclosed within its own brackets. Each condition will return an array of TRUE and FALSE values dependent on the result of that logical test. The asterisk (*) is used to multiply the corresponding values of the two arrays. This applies AND logic between the two conditions.

The asterisk, or multiply symbol, is used for AND logic because you only get a non-zero value if all logical tests return TRUE. For example, 1*1=1 but 1*0=0 and 0*1=0.

The resulting array from the conditions is then multiplied by the corresponding values in the [Total] column. And the resulting values from that are then summed by SUMPRODUCT.

To perform OR logic between conditions with SUMPRODUCT, the plus symbol (+) is used. Let’s see an example.

Why is the plus symbol used for OR logic? Let’s dive into the detail.

These values are then multiplied by the corresponding value from the [Total] column and summed by SUMPRODUCT.

By adding the corresponding values returned by all conditions, as long as one of the logical tests returns TRUE, then the overall result is TRUE. This is perfect OR logic.

If we tested multiple columns, and more than one column returned TRUE, this is fine. Any non-zero value evaluates to TRUE. So, (TRUE + TRUE) = TRUE.

Let’s see one final example, where we will use a combination of both AND and OR logic in a SUMPRODUCT function. These conditional tests can, of course, get more complex than we will take them in this chapter.

In Figure 9-7, the following formula sums the [Total] values for the products entered in cells B3 and C3 for the store entered in cell D3:

=SUMPRODUCT(

((tblSales[Product Name]=B3)+(tblSales[Product Name]=C3))*

(tblSales[Store]=D3),

tblSales[Total]

)

In this formula, the OR segment is enclosed in its own set of brackets. This is to follow the mathematical order of precedence and ensure that the OR segment evaluates before applying the AND logical expression to the selected store.

Let’s break this down.

Count Values That Meet Multiple Criteria

To count values instead of summing them, we simply need to remove the array from the formula that contains the values to be summed.

All the conditions used in the examples so far have tested text values. So, let’s finish with an example that tests a numeric value.

In Figure 9-9, the following formula counts the number of sales for the store entered in cell B3 where the number of units sold was greater than or equal to the number entered in cell C3:

=SUMPRODUCT(

(tblSales[Store]=B3)*(tblSales[Units Sold]>=C3)

)

Aggregating Multiple Columns

An area that showcases the flexibility of the SUMPRODUCT function well is its ability to handle criteria ranges of different dimensions.

Figure 9-10 shows a matrix of student names and their results to five different questions. “C” stands for correct and “I” for incorrect. A blank cell represents no answer to the question.

We want to return the number of correct questions answered for the student specified in cell B2. Cell B2 has been named [rngStudent].

The following COUNTIFS function fails to produce a result because the IFS functions (SUMIFS, COUNTIFS, etc.) require the criteria ranges to be of the same dimensions. And in this example, the student names’ range is one column wide, while the questions’ range is five columns wide.

=COUNTIFS(A5:A8,rngStudent,B5:F8,"C")

This task is no problem for SUMPRODUCT. It can easily handle criteria ranges of irregular dimensions.

In Figure 9-11, the following SUMPRODUCT formula is used to return the number of correct answers for the student “Jason”:

=SUMPRODUCT((A5:A8=rngStudent)*(B5:F8="C"))

Distinct Count Formula

A function that is unfortunately missing from the library of functions in Excel is one that counts only the distinct items in a range.

Figure 9-12 shows a list of webinars that have been delivered. We would like to return the distinct number of different webinars delivered.

This list is formatted as a table named [tblWebinars]. We will achieve the distinct count with a formula that combines the SUMPRODUCT and COUNTIFS functions together.

In Figure 9-13, the following formula is entered in cell D6 to return the distinct count of values in the [Webinar] column:

=SUMPRODUCT(1/COUNTIFS(tblWebinars[Webinar],tblWebinars[Webinar]))

Let’s break down how this formula works.

SUMPRODUCT then sums the values in this array.

This is a nice technique to produce a distinct count. It takes advantage of SUMPRODUCT’s ability to work with arrays and the fabulously useful COUNTIFS function.

Weighted Average

Excel also does not have a built-in function to calculate a weighted average. Fortunately, SUMPRODUCT with its ability to handle arrays natively makes this task simple.

In Figure 9-14, we have a table named [tblAssessments]. The arithmetic mean has been calculated using the AVERAGE function on the [Score] column and returns 78.2.

However, this is wrong because the assessments are not weighted equally. Some assessments make a greater contribution to the final average than others. This is acknowledged by the [Weight] column. For example, the final exam carries the most weight toward the final average.

The following formula is shown in Figure 9-15. The values in the [Score] column are multiplied by the values in the [Weight] column. This produces an array of values for SUMPRODUCT to sum.

=SUMPRODUCT(tblAssessments[Score]*tblAssessments[Weight])

Sum Every Nth Cell

You may come across a scenario where the values you want to sum are not in consecutive cells. Figure 9-16 shows a range that includes quarterly totals in every fourth column (every quarter).

We want to sum only the quarter values as they are subtotals of the months in that quarter. Of course, Figure 9-16 shows only two quarters, but imagine this is a larger range. In a different dataset, there may be many columns occurring every Nth column that you want to sum.

Two is subtracted from each of these column numbers. This is because the range starts in column C, the third column. So, this offsets columns A and B. If the range used started from column A, nothing would need to be subtracted.

From here, the TRUE and FALSE (1 and 0) values are multiplied by the values in range C2:J2, and the resulting values are summed by SUMPRODUCT.

This formula could easily be adapted to sum every Nth row. The main change would be to swap the COLUMN function for the ROW function.

Sum Values for a Specific Weekday

The SUMPRODUCT function is a built-in array formula. Its ability to work with arrays is one of its great strengths. This makes the function incredibly valuable to users of older versions of Excel that cannot natively handle arrays (discussed in Chapter 10).

We have seen examples of SUMPRODUCT working with arrays already in the last two examples. However, this example is here to demonstrate a common scenario that avoids the need for creating helper columns.

In Figure 9-18, we have a table named [tblDailySales] with sales transactions on different dates. We want to sum only the values that occurred at a weekend (Saturday or Sunday).

A common solution is to insert a column and use the WEEKDAY function to return the weekday numbers in all cells of that column. Then use a separate function such as SUMIFS to sum the [Total] values using the weekday number column for the criteria range. Functions like SUMIFS cannot handle the WEEKDAY function directly within its criteria.

With SUMPRODUCT, it can store the weekday numbers returned by WEEKDAY in an array. No need to create an additional physical column. This array of weekday numbers is then tested and calculated all in one formula.

In Figure 9-19, the following formula uses the WEEKDAY function to return the weekday numbers for each date. The 2 within the WEEKDAY function specifies that the weekday numbers should begin with Monday as 1 and end with Sunday as 7.

Without the use of an array function, you would not be able to provide an entire column of values to a function such as WEEKDAY. This also applies to functions such as LEFT, RIGHT, MONTH, etc. By using SUMPRODUCT, we do not have to force an array formula by pressing Ctrl + Shift + Enter.

Sum the Top Five Values Only

For the final SUMPRODUCT examples, let’s see it being used to sum the top five values in a range only. Once again, this is a showcase of SUMPRODUCT handling arrays.

Figure 9-20 shows the first eight rows of the [tblSales] table that we worked with earlier in this chapter when summing and counting values that met multiple criteria. We will use this table again now and look to sum the top five values only.

To do this, we will combine SUMPRODUCT with the LARGE function. The LARGE function returns the kth largest value in a range, for example, the second, third, or fifth largest value.

We want to return the top five values for SUMPRODUCT to sum, not just a single value. So, for the k argument, we will feed the LARGE function an array of constants containing all the values that we want.

In Figure 9-21, the following formula sums the top five values only:

=SUMPRODUCT(

LARGE(tblSales[Total],{1,2,3,4,5})

)

The LARGE function could be replaced by the SMALL function to return the smallest values to be summed.

Let’s now take this a step further and add criteria to the example. Say, we want to sum the top five values for a specific store only.

This ensures that the array the LARGE function returns values from only includes values where that criterion was met. Additional criteria could be added just as was discussed earlier in this chapter with SUMPRODUCT.

The importance of SUMPRODUCT in older versions of Excel is indisputable, from their general purpose of calculating the products of arrays followed by a sum to complex criteria sums and counts and finally its ability to natively handle arrays.

Ignoring Hidden Rows

When using functions such as SUM and AVERAGE on a range that contains hidden rows, the hidden values are still included by the function.

The AGGREGATE function can be requested to perform calculations on the visible cells only, ignoring any hidden rows, which are probably the result of filtering a list.

In the formula, the number 9 is the index number for the SUM function, and number 5 specifies the option to ignore hidden rows.

Let’s now look at an example where we want to return the second largest value in the [tblSales] table while ignoring hidden rows. When using the LARGE function, we will be required to use the K argument.

In Figure 9-28, the following formula specifies function number 14 – the LARGE function. Position 2 is specified for the K argument.

=AGGREGATE(14,5,tblSales[Total],2)

The “West” values are filtered out of the list. In Figure 9-26, you can see that there is a total value of 436.53 for the “West.” This is the second largest value in the complete list.

As the AGGREGATE formula in Figure 9-28 is requested to ignore hidden rows, it returns the correct result of 424.30.

Ignoring Errors in a Range

Functions such as SUM, LARGE, and MEDIAN will not work if a table column or range contains error values. However, the AGGREGATE function has a nifty option to ignore error values.

The AGGREGATE function provides seven different options. These include the individual options to ignore hidden rows, ignore error values, and ignore other totals. But there are also combined options.

A filter is applied to the table to hide the sales of “Hot Chocolate” and “Wine” in the [Product Name] column.

Adding Criteria to AGGREGATE

The AGGREGATE function, like SUMPRODUCT, can process arrays. This makes it a very useful function for those on older versions of Excel, as it negates the requirement to press Ctrl + Shift + Enter when entering an array formula.

This is a fantastic functionality; however, options such as ignore hidden rows cannot be used when adding criteria to the AGGREGATE function.

This is a shame as it removes one of the key strengths of AGGREGATE, but it can still be a very useful technique.

A criterion has been added within a set of brackets and multiplied by the [Total] column values, just like we did in SUMPRODUCT.

Function number 14 is used to specify the LARGE function, and option 4 is used to ignore nothing. Remember, when adding criteria in an array, options such as hidden rows will not be applied anyhow.

Criteria can only be added like this, for the six functions that can handle arrays, as stated earlier in the chapter.

Function Specified by a Cell Value

For the final examples, let’s use a drop-down list to make it easy for the user to select the function they would like to apply. The user can simply select the aggregate function from a list, instead of typing those awkward index numbers in.

Figure 9-32 shows a table named [tblFunctions]. The [Function] column contains five functions that are being used as the source for a drop-down list. The [Index] column contains the index number associated with each of these functions.

Figure 9-33 shows the drop-down list of functions in cell E2 above [tblSales].

We will use the VLOOKUP function (other lookup functions are covered in Chapter 11) to look up the selected function in cell E3 within [tblFunctions] and return the index number for the AGGREGATE function to use.

The VLOOKUP function is nested within the function num argument of AGGREGATE. It returns the function number from [tblFunctions] for the selected function in E2.

Option 7 is specified to ignore hidden rows and error values. A filter has been applied to the [Product Name] column to exclude “Wine” and also to the [Region] column to exclude “South.”

With the 19 different functions on offer by AGGREGATE, it makes it simple to create this interactivity and enable a user to choose the function from a nice drop-down list.