Here’s how we can use SUMPRODUCT instead of SUMIF in Excel when adding columns of data based on a certain criteria:
(Note: Video version above, text version below)
Text version (note: click on an image for a better view):
I have a small data range and in column “G” I want to to enter a formula that will add columns “B:F” if column “A” is equal to cell “L1”:
The syntax for SUMIF is =SUMIF(range,criteria,[sum_range]). In our example we have:
range = A2
criteria = L1
sum_range = B2:F2
The formula would be =SUMIF(A2,$L$1,B2:F2). When we enter that formula in G2 and set L1 to “Mexico” so it matches A2, we get:
Unfortunately, that’s not correct. The reason is that SUMIF cannot handle multiple columns, only multiple rows. So the $125 is the value of the first column, column B. The sum should be $169.60.
Now, let’s try SUMPRODUCT. The syntax for SUMPRODUCT is =SUMPRODUCT(array1,[array2],[array3],…). SUMPRODUCT takes the values from each array and multiplies them together and adds up the results. Our formula will be =SUMPRODUCT((A2=$L$1)*(B2:F2)). Here we are taking the formula A2-$L$1 and multiplying it times the sum of B2:F2. Since a TRUE for the first array = 1 and a FALSE = 0, if the first array is TRUE, the result should be the sum of the values in B2:F2, if FALSE then 0. Let’s see what happens:
Notice that both formulas accurately determined the match between column A and cell L1, but only the SUMPRODUCT formula added the column data correctly.
An alternative to the SUMPRODUCT function would be =(A2=$L$1)*SUM(B2:F2) with a similar concept.