By Victor Alarsa
If you are one of those who loves to model using lots of “IF”, “AND” and “OR” functions, then this article is for you.
Let’s discuss the SUMPRODUCT, which literally means summing up the multiplication (product) of two or more different arrays.
For example, if you want to calculate total rental value of a property with different floor sizes and rents per square foot, then you simply need to multiply each floor size by its corresponding rent and sum up everything in the end.
Total Rent = (Rent 1 x Size 1) + (Rent 2 x Size 2) + (Rent 3 x Size 3) + …. + (Rent N x Size N)
However, you might want to take the shortest route, which is using SUMPRODUCT. It is as simple as in the example below (on the left): you just need to select the arrays you want to multiply. In this case, column “F” and “G”:
SUMPRODUCT = ( Sq. ft Array * Rent/Sq. ft Array )
SUMPRODUCT = ( F2 : F4 * G2 : G4 )
This will result in the same value as in the first example, but it is faster (PS: in this case, you can either use a “star” (*) or “comma” (,) to split the two arrays).
From now on, this part of the SUMPRODUCT formula will be called: “Quantity Column”.
Now, let’s make things more interesting. Let’s say, for example, that you want to do the SUMPRODUCT only for those properties located in Oxford. For that, we need to start using the “arguments”. The argument is the first part of the formula where you are going to write down which criteria the SUMPRODUCT must follow. In this example, we start the SUMPRODUCT formula by selecting the Location Array and equaling to Oxford
( Location Array = Oxford )
( E2 : E4 = G7 )
The way Excel is going to read the formula is: if the statement is TRUE, then it places a “1”, or, if the statement is FALSE, it places a “zero”.
For the last step, you just need to do what we have done in the example above, adding the ‘Quantity Column’ ->
SUMPRODUCT = ( ( E2 : E4 = G7 ) * (F2 : F4 * G2 : G4 ))
The result will be the Rent value of the Oxford row only.
The next example shows us when the SUMPRODUCT beats the SUMIFS. The SUMIFS does not allow the “OR” function in its criteria, but SUMPRODUCT does. Say you want to SUMPRODUCT not only those properties from Oxford, but London as well. That’s simple. In the argument fragment you will need to type
( Location Array = London ) + ( Location Array = Oxford )
( E2 : E4 = G7 ) + ( E2 : E4 = H7 )
The golden rule here is:
For “AND” you will use a star (*) and for “OR” you will use a plus (+).
So, in this example we are going to use OR, thus, we are going to use a plus (+).
To finish the formula, you just need to add the ‘Quantity Column’.
SUMPRODUCT = ( ( ( E2 : E4 = G7 ) + ( E2 : E4 = H7 ) ) * (F2 : F4) * (G2 : G4 ) ).
Tip! Always pay extra attention to the brackets, at this level, there are many.
Finally, the most challenging example we are going to see in this article.
What should we do if we need an “AND” and an “OR” criteria at the same time? Say that we want to select only properties with 750 sq.ft and those located either in London or Oxford.
We start by using the same fragment as before:
( ( Location Array = London ) OR ( Location Array = Oxford ) ) AND ( Sq.ft Array = 750 ) ( ( Location Array = London ) + ( Location Array = Oxford ) ) * ( Sq.ft Array = 750 ) ( ( E2 : E4 = G7 ) + ( E2 : E4 = H7 ) ) * ( G2 : G4 = G8 ) )
After completing the criteria, you just need to add the ‘Quantity Column’ and it’s done! SUMPRODUCT = ((( E2 : E4 = G7 ) + ( E2 : E4 = H7 ) ) * ( G2 : G4 = G8 ) * (F2 : F4 )* (G2 : G4 ))
In the end it is quite simple.
I hope that from now on, you will find it easier to use the SUMPRODUCT function on your daily real estate financial modelling tasks.
Get in touch if you have any queries!