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.

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.

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.

In my earlier blog ” Flipping from horizontal to vertical, journal entry” I was using INDEX function. However, I thought maybe the INDEX should have been introduced more profoundly first.

A sales report is downloaded from ERP to Excel.

The data should be coherent for pivot report. Date, product and sales volume need to be separated to own columns.

INDEX formula was made for this purpose, also for many other purposes, but at least for this case.

Range A3:D21 is defined as array. After array, row number and column numbers are entered. Row and columns are like coordinates with piece of information is taken from the array.

For example, in our array, array row 2 column 2 equals to 76. Our array row 4 col 1 equals to 2.1.2019.

This is how tacked the case. Report data is in report sheet and pivot data in pivot sheet.

The solution is built with two parts: columns A to C contain the data and columns E to J the column and row values for data.

The date is defined in E and F columns.

As the date is same for three consecutive rows. Therefore E-columns has a new value for every three rows. Row F has a fixed value 1 as the date is in the first column throughout in report data.

The product information is on the top of the report. The row values in G column is static one, column values in column H is varying from 2 to 4.

The sales volume begins in the second row. As sales data is side by side, I column changes value after having had three similar values. The column coordinate value in J column goes 2,3,4,2,3,4.

Here are the cell contents as I have written them.

The date is in A column. In the array the first date is in the second row and first column. B column is for product information. In the report the product is in the first row. The sales volume in C column is extracted starting from second row, columns two to four.

If you want to know how to change all the formula text visible instead of formula values, take file-options and then in advanced page display options for this worksheet, activate “Show formulas in cells instead of their calculated results”.

This blog was to clarify how INDEX function functions.

Product code consists of five letters. A report should count sales for any product codes which include at least one H letter.

Random product codes I have created with following function:

=CHAR(RANDBETWEEN(65;85)) & CHAR(RANDBETWEEN(65;85)) & CHAR(RANDBETWEEN(65;85)) & CHAR(RANDBETWEEN(65;85)) & CHAR(RANDBETWEEN(65;85))

And sales :

=RANDBETWEEN(1;100)

If any of five letters is H, then the report should count the sales for that product.

I have solved this issue with SEARCH-function.

Search function returns value if the text to be found was found.

To differentiate the two results, you can use ISERROR function. The function returns value based on whether the cell contains an error.

If SEARCH function finds a value, the result is not an error. If SEARCH does not find the value, then the result is an error.

Now we can build the formula to solve the issue.

=IF(ISERROR(SEARCH(“H”;C5));””;”H here”)

The letter H is search in the cell C5. If the result is an error, there is not H in cell C5, then the result is blank. If SEARCH finds an H in C5, then the result is not an error, and IF returns the value H here.

The records, where H exist, are marked with H here text. Now we should count the sum for H here records.

=SUMIF(E5:E28;”H here”;D5:D28)

E-column holds the IF function to check H letters, D-column is for the sales volumes.

This is a matrix which is a base for journal entry. The first row defines whether posting is for debit or credit side. The second level is account and third posting balance. The first posting should be debit z 10, credit 7 10, second debit z 15, credit 5 15 and so on.

The idea is to have some automation to fill Excel template which is imported to ERP to create a journal entry in general ledger. Some Excel functions are needed that the whole process would not be manual.

The matrix in this case holds just few records, but if the matrix was 100 records long, it would take long time to enter all the values separately manually to Excel.

We need INDEX function in this case. First you need to define matrix, after that you give row and columns coordinates inside matrix.

We need INDEX function in this case. First you need to define matrix, after that you give row and columns coordinates inside matrix.

To define z as debit account, we need to create account codes as matrix with one line. Debit account z is the first value in matrix, credit account 7 is the second value in matrix.

The posting balances are another one line matrix, but the first value should be blank, the second value is 10. Then balance 10 is in line with first credit account 7.

Range(B8:D19) is a basis for journal entry in more suitable format to be uploaded to ERP. This is only simplified example, you need also other inputs like posting date, description and so on.

Another modification is to have just one posting for debit side, then balance for z is 135. For me, it is clearer to have each debit and credit paired as above.

A list of birthdays is reported. Persons should be divided into two groups. First group consists of persons under 21 years of age. Persons 21 or above belong to the second group.

First we need to define what is the birthday for those people who are exactly 21 years of age. Anyone whose birthday is later, belongs to group “under 21”. Who was born exactly 21 years ago or earlier belongs to “21 or older”.

Function =TODAY() returns the current date. Function DATE consists of three parts: year, month, and day. Number category for the cell E2 is date.

Here is a simple example about DATE function.

Date today is Dec 30^th 2018. The border line is the date 21 years ago, the date can be calculated with DATE function. Year in DATE function is current year minus 21, the month is current month and day is current day. If the birthday of a person is exactly border line or earlier then the person is 21 or older. If person is born later than border line, then the person is under 21.

The values in cells B4:B24 are random values. You can enter values manually to test the solution. However, if you want to limit your manual work, you can use RANDBETWEEN formula. Using DATE nested formula inside RANDBETWEEN you can define lower and upper limit for random values.

The formulas are here for copy and paste:

=DATE(YEAR(F2)-21;MONTH(F2);DAY(F2))

=IF($I$2>=B4;”21 or above”;”Under 21″)

=RANDBETWEEN(DATE(1960;1;1);DATE(2015;1;1))

Another solution is to use DATEDIF function.

DATEDÍF requires earlier date, later date and type and reporting unit. B4 cell holds the birthday, TODAY() is the current date, y means that value is returned in years.

When you set DATEDIF inside IF function, function returns you the wanted result.