In this tutorial we are going to use IF, SUMPRODUCT, and ISNUMBER to figure out the difference between summing values that add up to zero vs. summing blank cells in Excel.
You can download the file here and follow along. If you get a preview, look for the download arrow in the upper right hand corner.
I have a list here of 4 accounts for two months. Each account has a debit and credit transaction, although in a couple cases there are no transactions so the cells are blank:
I want to pull the results from this data into another data range like this:
Notice the results I want to appear in column “H”:
– Adding up to zero = “Paid”
– A positive or negative balance = “Amount Pending”
– If they are blank = “No Transactions”
I thought I would use a combination of nested IFs and SUMIFS in a formula like this:
=IF(SUMIFS($D$2:$D$17,$B$2:$B$17,F2,$C$2:$C$17,G2)=0,”Paid”,IF(SUMIFS($D$2:$D$17,$B$2:$B$17,F2,$C$2:$C$17,G2)<>0,”Amount Pending”,”No Transactions”))
However, notice in column “J” where I wanted “No Transactions” I got “Paid” instead:
The reason is that Excel treats blanks just like zero when summing those cells.
The solution I found, with the help of some very smart experts in an Excel group on LinkedIn was:
=IF(SUMPRODUCT(($B$2:$B$17=F2)*($C$2:$C$17=G2)*(ISNUMBER($D$2:$D$17)))>0,IF(SUMPRODUCT(($B$2:$B$17=F2)*($C$2:$C$17=G2)*($D$2:$D$17))=0,”Paid”,”Amount Pending”),”No Transactions”)
Let’s break down the formula into three parts:
First, IF the result of the SUMPRODUCT function is greater than zero:
Then run the next IF statement that says, if the result of the SUMPRODUCT function equals zero, then “Paid”, if not then “Amount Pending”:
If the result of the first IF statement is false, meaning it equals zero, then:
How does SUMPRODUCT work? Excel says SUMPRODUCT “Returns the sum of the products of corresponding ranges or arrays”.
So Excel is going to run these three arrays:
The results of which will be a series of either TRUES or FALSES. Remember, in Excel, TRUE = 1 and FALSE = 0. It will then add up those results (SUM the PRODUCTS as the name SUMPRODUCT suggests) and if the result is greater than zero, run the next IF statement. If it equals zero, then insert “No Transactions”.
To really get a good explanation of how the SUMPRODUCT function works in this formula, go to the 4:10 point in the video and it goes into far more detail that I can properly explain here. I think you’ll find it worthwhile.
What can you do next?
Share this post with others that can benefit from it!
Leave a comment or reply below – let me know what you think!
Subscribe to this blog for more great tips in the future!
Check out my YouTube channel – click on the YouTube icon below!