How to Use SUMIFS with Multiple Criteria in Same Column in Excel

Using SUMIFS with multiple criteria in the same column in Excel allows you to sum the values in a range based on multiple conditions. The SUMIFS function can handle several criteria, even when they're applied to the same column.

To use SUMIFS with multiple criteria in the same column, you'll need to use an array constant for the criteria and then use the SUM function to add up the individual results.

Here's a step-by-step guide on how to use SUMIFS with multiple criteria in the same column in Excel:

1. Prepare your data in Excel, including the column that you want to sum and the column with the criteria.
2. Identify the range of cells that you want to sum, the range of cells containing the criteria, and the multiple criteria you want to apply.
3. In a blank cell, type the following formula, replacing the placeholders with the appropriate cell ranges and criteria:

=SUM(SUMIFS(sum_range, criteria_range, {"criteria1", "criteria2", ...}))

Here's an example:

Example

Suppose we have the following data in an Excel worksheet:

  A         B
1 Sales     Region
2 1000      East
3 2000      West
4 3000      East
5 4000      North
6 5000      South
7 6000      West
8 7000      East

We want to sum the sales from both the East and West regions. Using the SUMIFS function with multiple criteria in the same column, we can achieve this.

1. The range of cells we want to sum is B2:B8 (Sales).
2. The range of cells containing the criteria is A2:A8 (Region).
3. The criteria we want to use are "East" and "West".

Now, we can use the following formula to calculate the sum of sales from both the East and West regions:

=SUM(SUMIFS(B2:B8, A2:A8, {"East", "West"}))

The result will be 18000, as the sum of sales for East and West regions is 1000 + 2000 + 3000 + 6000 + 7000 = 18000.