While working with Excel, we are able to sum values that satisfy more than one criteria by using the SUMIF, SUM and SUMPRODUCT functions. This step by step tutorial will assist all levels of Excel users in summing values that may contain either of two given criteria, employing three different methods.
Figure 1. Final result: Sum if equal to either x or y
Formula using SUMIF : =SUMIF(C3:C9,"Laptop",D3:D9)+SUMIF(C3:C9,"Tablet",D3:D9)
Formula using SUM and SUMIF : =SUM(SUMIF(C3:C9,{"Laptop","Tablet"},D3:D9))
Formula using SUMPRODUCT : =SUMPRODUCT(D3:D9*(C3:C9={"Laptop","Tablet"}))
Syntax of the SUMIF Function
SUMIF sums the values in a specified range, based on one given criteria
=SUMIF(range,criteria, [sum_range])
The parameters are:
- Range: the data range that will be evaluated using the criteria
- Criteria: the criteria or condition that determines which cells will be added
- Sum_range: the cells that will be added; if left blank, “sum_range” = “range” which means that the range of data that will be added is the same range of data evaluated
Syntax of SUM Function
SUM adds all given values
=SUM(number1,[number2],...])
The parameters are:
- number1 – any number, array or cell reference whose values we want to add
- Only number1 is required; succeeding numbers are optional
Syntax of the SUMPRODUCT Function
SUMPRODUCT returns the sum of the products of two or more components in the given arrays
=SUMPRODUCT(array1, [array2], [array3], ...)
The parameters are:
- array – a range of values or cells whose values we want to multiply with other values and then add
- All array arguments must be in the same size
- Non-numeric values in an array are treated as zeros
Setting up the Data
Our table has three columns: Month (column B), Item (column C) and Sales (column D). Cell G2 contains our criteria “Laptop or Tablet”. We want to obtain the total sales for “Laptop” and “Tablet”. We enter the results in cells G3, G4 and G5 using three different formulas.
Figure 2. Sample data to sum if equal to either x or y
Sum of laptop or tablet sales using SUMIF
SUMIF function can only handle one criteria. In order to determine the sum based on two criteria, we use two SUMIF functions. Let us follow these steps:
Step 1. Select cell G3
Step 2. Enter the formula: =SUMIF(C3:C9,"Laptop",D3:D9)+SUMIF(C3:C9,"Tablet",D3:D9)
Step 3. Press Enter
The range of the data we want to evaluate is C3:C9. The are two criteria, one for each SUMIF function :“Laptop” and “Tablet”. We want to sum the sales in D3:D9 based on the given criteria.
As a result, the total sales for Laptop and Tablet in cell G3 is $70,000.
Figure 3. Using two SUMIF functions to sum sales for Laptop or Tablet
Sum of laptop or tablet sales using SUM and SUMIF
The second formula uses a combination of the SUM and SUMIF functions to sum sales based on two criteria. Let us follow these steps:
Step 1. Select cell G4
Step 2. Enter the formula: =SUM(SUMIF(C3:C9,{"Laptop","Tablet"},D3:D9))
Step 3. Press Enter
The range we want to evaluate is still C3:C9. The criteria is presented as an array inside the SUMIF function: {“Laptop“,”Tablet“}. Our formula sums the sales in D3:D9 if either of the criteria is met in C3:C9.
The final result for the total sales for Laptop and Tablet in cell G4 is $70,000, which is the same as in the previous example.
Figure 4. Using SUM and SUMIF to sum sales for Laptop or Tablet
Sum of laptop or tablet sales using SUMPRODUCT
The third formula uses the SUMPRODUCT function to sum sales based on two criteria. Let us follow these steps:
Step 1. Select cell G5
Step 2. Enter the formula: =SUMPRODUCT(D3:D9*(C3:C9={"Laptop","Tablet"}))
Step 3. Press Enter
Our third formula is somehow different because it evaluates C3:C9 based on the criteria {“Laptop“,”Tablet“} and returns as array of TRUE or FALSE for each item. The resulting array is:
{TRUE,FALSE;FALSE,TRUE;FALSE,FALSE;FALSE,TRUE;FALSE,FALSE;FALSE,FALSE; TRUE,FALSE}
SUMPRODUCT then sums the sales in D3:D9 for every TRUE result in the array.
Finally, the result in cell G5 is $70,000, which is the same as in the previous examples.
Figure 5. Using SUMPRODUCT to sum sales for Laptop or Tablet
Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.
Leave a Comment