SUMPRODUCT and IF function
You don't need to use the IF function in a SUMPRODUCT function, it is enough to use a logical expression. For example, the array formula above in cell B12 counts all cells in C3:C9 that are above 5 using an IF function.
The first argument in the IF function is a logical expression, use that in your SUMPRODUCT formula. The formula in B13 does the same thing as in B12.
You need to tell Excel the order of calculation, in other words, make sure you calculate C3:C9>5 before you multiply by 1. To do that use parentheses.
The larger than comparison operator compares each value in C3:C9 if larger than 5. It returns an array the same size as C3:C9 containing boolean values, TRUE or FALSE.
The picture above shows the array in column D, value 7 and 8 are larger than 5.
The SUMPRODUCT function can't sum boolean values so multiply (using the asterisk *) the logical expression by 1 to convert the boolean values (TRUE and FALSE) to numbers (1 or 0).
The asterisk also has another advantage, you can enter the SUMPRODUCT formula as a regular formula.
Combine SUMPRODUCT and IF
The picture above demonstrates an IF function that checks if a condition is met and if TRUE returns a value in a corresponding cell.
There is no need for the IF function in the above example, simply use the logical expression and multiply with the values you want to use, demonstrated in cell B13.
B3:B9="A" returns {TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE} displayed in column A in image above.
(B3:B9="A")*C3:C9 returns this array: {2;0;7;0;8;0;0} shown in column A.
Lastly, the SUMPRODUCT function sums all the numbers in the array returning 17 in cell B12.
2 + 7 + 8 = 17.
Download excel *.xlsx file
Weekly Blog EMAIL
Welcome! I am Oscar and here to help you out.Feel free to comment and ask Excel questions.
Make sure you subscribe to my newsletter so you don't miss new blog articles.
Related articles
How to do tiered calculations in one formula
The image above demonstrates a formula that calculates tiered values based on a tier table and returns a total. This […]
SUMPRODUCT – multiple criteria
The formula above in cell G8 uses two conditions in cell G2 and G3 and a date range G5:G6 to […]
How to use the SUMPRODUCT function
The SUMPRODUCT function calculates the product of corresponding values and then returns the sum of each multiplication.
SUMPRODUCT and nested IF functions
I have demonstrated in a previous post how to simplify nested IF functions, in this article I will show you how […]
The above image demonstrates how to ignore blank cells in a SUMPRODUCT formula. The following formula is shown in cell E3. […]
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
