Welcome to Excel Jet Consult blog post. IN this blog post, we will learn how to perform COUNTIF and SUMIF operation using the SUMPRODUCT function. Let’s get started
SAMPLE DATA
In the screenshot below, we’ve got a sales table with six columns. In cells H5 to H8, we’ve got the unique list of the Products which we are going to use to perform the single criteria operation.
SUMPRODUCT FUNCTION
The SUMPRODUCT function returns the sum of the products of corresponding ranges or arrays. The default operation is multiplication, but addition, subtraction, and division are also possible.
Syntax
To use the default operation (multiplication):
=SUMPRODUCT(array1, [array2], [array3], …)
The SUMPRODUCT function syntax has the following arguments:
array1 – The first array argument whose components you want to multiply and then add. Required argument
[array2],[array2] – The first array argument whose components you want to multiply and then add. Optional arguments
COUNTIF & SUMIF using SUMPRODUCT Function
In cell I5, execute the formula: =SUMPRODUCT(–($C$2:$C$22=H5))
Click Enter and Drag down the formula using fill handle
In the formula above, the array1 is the $C$2:$C$22 in absolute cell reference and we used the equal to operator to check whether the values from the column C is equal to the unique product in cell H5 downwards. In addition, we used the double negative to coerce the 1 for true and 0 for false
From the screenshot below, we can see that there are 6 transactions for products that equals to Laptop.
To do the same for the SUMIF,
In cell J5, execute the formula: =SUMPRODUCT(–($C$2:$C$22=H5),$F$2:$F$22)
Click Enter and Drag down the formula using fill handle
In the formula above, the array1 is the $C$2:$C$22 in absolute cell reference and we used the equal to operator to check whether the values from the column C is equal to the unique product in cell H5 downwards. Furthermore, the array2 is $F$2:$F$22 in absolute cell reference which will return the corresponding Total for each of the Products. In addition, we used the double negative to coerce the 1 for true and 0 for false
From the screenshot below, we can see that the Total Sales for Laptop product is 1345.
In conclusion, the SUMPRODUCT function is one of the oldest and versatile functions in Excel that can efficiently handle the operations of many functions.
See you in the next post