Sum if equal to either x or y

The Excel SUMIF function is used to perform the addition of cells that meet a single criterion. This function can be used to perform addition of cells based on numbers, dates, and text that match certain criteria. The SUMIF function can also be used to sum if equal to either X or Y in Excel.

Formula

=SUMIF(range, criteria1, sum_range) + SUMIF(range,criteria1,sum_range)

Explanation

The formula incorporating Excel SUMIF function to sum if equal to either x or y uses the following arguments:

  • range (required): This is that range of cells to which you want to apply the criteria against.
  • criteria1 (required): This is the first criteria which determine which cells we need to add.
  • criteria2 (required): This is the second criteria which determine which cells we need to add.
  • sum_range (required): These are the cells which are to be added together.

This works as follows:

  • First, the criteria are applied to the range of cells which are specified by the range.
  • The reference obtains the criteria and then sum_range specifies the cells that are to be added together.
  • Every instance of the Excel SUMIF function gives a subtotal. The formula simply adds the two results together.  

Example 1

In this example, what we plan to do is add the sales from the East and South regions together. We can do this by 3 different formulas;

Using SUMIF function;

=SUMIF(C5:C11,“East”, E5:E11) + SUMIF(C5:C11,“South”, E5:E11)

This works as follows:

  • First, the criteria are applied to the range of cells (region) which are specified by the range.
  • The reference obtains the criteria and then sum_range (Amount) specifies the cells that are to be added together.
  • Every instance of the Excel SUMIF function gives a subtotal. The formula simply adds the two results together which comes out to be 57000.

Figure 1. Example of SUMIF Function to sum if equal to either x or y

Using SUMIF and SUM functions;

=SUM(SUMIF(C5:C11,{“East”, “South”}, E5:E11))

This works as follows:

  • Here we use two Excel functions, SUM and SUMIF.
  • The SUMIF function returns one value for every item in the criteria and the SUM function simply adds them together.

Figure 2. Example of SUMIF and SUM Functions to sum if equal to either x or y

Using SUMPRODUCT function;

=SUMPRODUCT(E5:E11*((C5:C11= “East”)+(C5:C11= “South”)))

The SUMPRODUCT function can also be used with the OR logic. The alternative way to write the same formula;

=SUMPRODUCT(E5:E11*(C5:C11={ “East”, “South”}))

 Figure 3. Example of SUMPRODUCT functions to sum if equal to either x or y

Notes

If you wish to input supply criteria as a range (reference), you will have to enter an array formula by pressing control + shift + enter:

{=SUM(SUMIF(Region, Criteria, Amount))}

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar