Given the importance of summarising values as part of the report creation process, the Excel SUMIF() function has always been a favourite of many accountants. With the release of Excel 2007, the newly-introduced SUMIFS() function went far beyond the capabilities of SUMIF() by increasing the maximum number of criteria from 1 to 174. This made it much easier to create conditional sums that were based on multiple columns or that used multiple criteria based on the same column, such as dates falling between the first of the month and the last of the month. Prior to SUMIFS() handling multiple conditions like this would often require the use of an Excel array formula, either entered directly or ‘wrapped’ in the SUMPRODUCT() function.

Arrays and Boolean logic

An Excel formula can work with blocks of cells as well as single cells. As an example, let’s see what happens when we try to multiply two blocks of three cells together inside a sum function (see Figure 1 below).

=SUM(A1:A3*B1:B3) will return a #VALUE! error, but we can force Excel to evaluate our two blocks of three cells, cell by cell. We do this, not by changing the formula itself, but instead by saving exactly the same formula using the key combination Control+Shift+Enter rather than just the Enter key. This creates our array formula and surrounds it in curly brackets to show that it is an array formula: {=SUM(A1:A3*B1:B3)}

Our formula will now be evaluated as the result of (A1*B1)+(A2*B2)+(A3*B3) (see Figure 2 below).

We can use this technique to achieve the same result as the SUMIF() and SUMIFS() functions (see Figure 3 below).

The formula =(A4=$A$1) will return either TRUE or FALSE. Used as part of a mathematical operation, TRUE becomes 1 and FALSE becomes 0. Anything multiplied by any number of ones stays the same, anything multiplied by one or more zeros will be zero. This allows us to multiply a value by one or more TRUE/FALSE statements in order to apply the conditions for our conditional sum formula.

As an example, to add up all the values in column B, where the corresponding row in column A, contains the word Red we could use SUMIFS(): =SUMIFS($B$4:$B$6,$A$4:$A$6,A1)

We could achieve the same result using arrays and Boolean logic instead: {=SUM($B$4:$B$6*($A$4:$A$6=A1))}

Although using array formulae can be useful, it’s very easy to forget to use Control+Shift+Enter when editing, or even just looking at the formula. This stops it being an array formula and it will return an error or, even worse, the wrong result. For this reason, the SUMPRODUCT() function is often used just because it treats its arguments as arrays without the need to use Ctrl+Shift+Enter: =SUMPRODUCT($B$4:$B$6*($A$4:$A$6=A1))

Why use SUMPRODUCT() when we have SUMIFS()

Although SUMIFS() copes with simple equal to, not equal to, greater than, and less than comparisons, more complicated calculations, such as those that need to embed other functions, can’t be used. As an example, if we needed to add up all the values where the first letter of our description was “R”, with SUMIF() or SUMIFS() we would have to add a column to our table containing =LEFT(A4,1). We could then use this new column for our criteria range. We could do this using SUMPRODUCT() without the need for the additional column by embedding the LEFT() function within our conditional statement: =SUMPRODUCT($B$4:$B$6*(LEFT($A$4:$A$6,1)=A1))

We could also use the SUMPRODUCT() method to create a two-dimensional condition, as shown in Figure 4 below.

The formula in cell G2 multiplies the range containing all our values: B2:D6 by two statements. One compares the row headings to the value in cell F2 and the other compares the column headings to the value in G1: =SUMPRODUCT($B$2:$D$6*($A$2:$A$6=F2)*($B$1:$D$1=G1))

There is also a difference between the use of SUMIF() or SUMIFS() and the use of arrays, or the SUMPRODUCT() equivalent, when referring to content in another workbook. Both methods will work when that other workbook is open but, if it is closed, SUMIF() and SUMIFS() will return #VALUE! errors, whereas the array and SUMPRODUCT() formulae will continue to work in some cases. However, any references that use structured Table references, rather than normal cell references, will return #REF! if the workbook containing the Table is not open.