Get instant live expert help with Excel or Google Sheets
“My Excelchat expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.

Sum if equal to either x or y

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.

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:
Solution examples
Use the Vlookup Function to complete the "employee" column of table 2. Use "job Id" from table 2 as your lookup_value(s) and table 1 as your reference.
Solved by C. H. in 16 mins
If a cell in another sheet is populated I need a vlookup done. If the cell is not populated I need the cell to return blank.
Solved by T. D. in 60 mins
I am trying to make a chart that turns a week range red if nothing is entered in the range. If something is entered then I would like it to turn green. Please Help
Solved by E. U. in 43 mins
I need a check box to show/hide an answer of an if function
Solved by Z. U. in 23 mins
I am trying to have a tab change color when the Sum of a row = 0. I am using code on the tab but it appears to be confused because the value of the given cell is not 0 it is "=SUM(G4:G17)"
Solved by S. F. in 41 mins

Leave a Comment

avatar
Yo Yo
Guest
Yo Yo

Hi, I want to have a formula calculate in a cell and display the result only if YES is typed into a different cell. How can I do this?
Exact example: I want Cell N5 to calculate the SUM J5/AD5 only if YES is typed into Cell M5, otherwise remain blank or have a hyphen – if YES is not typed

Comment awaiting moderation

Subscribe to Excelchat.co

Get updates on helpful Excel topics

Subscribe to Excelchat.co

Did this post not answer your question?
Get a solution from connecting with the expert

Another blog reader asked this question today on Excelchat:

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc