Introducing SUMPRODUCT function

SUMPRODUCT is very versatile function. Only parameters you enter are multiple arrays. Therefore it can be used for multiple purposes in finance and accounting. I present here some examples, but there are much more.

The basic idea with SUMPRODUCT is multiply several arrays with each other.

Simply 3 * 1 = 3, 4 * 2 = 8 and 5 * 3 = 15. Sum of 3,8, and 15 equals to 26. You can end up with this result with SUMPRODUCT.

This blog is related to my earlier blog to count total debits and total credits. The same process is here done with SUMPRODUCT function.

In my example the total debits and credit have been counted with four different ways with SUMPRODUCT. Feel free, to find even more options to count the same process.

The content in cells C11:C14 is pasted here, so you can copy paste them, if needed.

=SUMPRODUCT($B$3:$M$8*($B$2:$M$2=C10))

=SUMPRODUCT(–($B$2:$M$2=C10)*$B$3:$M$8)

=SUMPRODUCT(–($B$3:$M$8)*($B$2:$M$2=C10))

=SUMPRODUCT(($B$2:$M$2=C10)*$B$3:$M$8)

All of these variations lead to the correct result. You need to define separately, range for debit and credit codes (B2:M2) and range for values (B3:M8). Please note, that arrays cannot be separated with semicolon but by multiplication sign.

One more additional use for SUMPRODUCT is to count total for account groups.

You have a list of income accounts. You want to count the total per account group. Like sum of accounts starting with 10. First array defines the values in range B3:B7, if two digits from left make up “10”. Sum range is D3:D7.

You can count the sum for account group with SUM and IF like this:

=SUM(IF(LEFT(B3:B7;2)=”10″;D3:D7))

Don’t forget control+shift+enter.

The debit and credit totals have the values vertically, account group total case horizontally. Still SUMPRODUCT tackles both the cases.

However, the criteria 10 needs to inside the formula not in a reference cell. Note also, that accounts values in B3:B7 are of text data type. The formula in G4 just does not work.

Related