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))}**

## Leave a Comment