Microsoft Excel is a powerful tool used for various purposes such as data analysis,
visualization and making calculations. Excel provides a wide range of formula functions
to perform calculations easily and efficiently. In this article, we will focus on the SUM
function and how to use it with multiple criteria.
SUM Function in Excel
The SUM function is a built-in function in Excel which adds the values of given cells or a range of cells. Its basic syntax is:
=SUM(value1, [value2], ...)
Where value1, value2, ... are the numeric cells, ranges, or arrays you want to add.
SUM with Multiple Criteria
In some cases, you might need to sum values based on multiple criteria. For instance, you want to calculate the total quantity of a specific product from a list containing multiple products. Excel provides several ways to perform this task.
Using SUMPRODUCT and Matrix Multiplication
The SUMPRODUCT function is a powerful function that provides the capability of
performing matrix multiplication. It returns the sum of the products of corresponding
entries in two or more arrays. We can use it to achieve our goal of summing the quantity with
multiple criteria.
Assume you have a table that looks like:
| Product | Date | Quantity |
|---|---|---|
| Product A | 01-Feb | 5 |
| Product B | 01-Feb | 3 |
| Product C | 01-Feb | 6 |
| Product A | 02-Feb | 4 |
Now, you want to calculate the total quantity of Product A Sold in February. To achieve this, you can use the following formula:
=SUMPRODUCT((Range_Product=Product_A)*(Range_Date=Month_Feb)*Quantity_Range)
Where:
Range_Productis the range of cells for the productProduct_Ais the specific product ("Product A" in this example)Range_Dateis the range of cells for the dateMonth_Febis the specific date for February (in our case, it can be 02-Feb for simplicity)Quantity_Rangeis the range of cells for the quantity
Using INDEX, MATCH and SUMPRODUCT
Another way to sum values based on multiple criteria is by using INDEX, MATCH,
and SUMPRODUCT functions. Assume you have a dataset as shown in the table above,
and you want to calculate the total quantity for Product B sold in February. Using
INDEX, MATCH and SUMPRODUCT functions, you can apply the following
formula:
=SUMPRODUCT(--(INDEX(Quantity_Range, MATCH(Product_B, Range_Product, 0), 0) = Quantity_Range),
--(Range_Date = Month_Feb))
Where:
Quantity_Rangeis the range of cells for quantityProduct_Bis the specific product ("Product B" in this example)Range_Productis the range of cells for the productRange_Dateis the range of cells for the dateMonth_Febis the specific date for February ("02-Feb" in this example)
- The
SUMfunction in Excel is used to add values - You can use multiple methods to sum values based on multiple criteria using Excel
- SUMPRODUCT, INDEX, and MATCH functions can be combined to achieve advanced lookup and sum calculations