When multiple conditions are applied in the same column, the SUMIFS function defaults to "AND" logic, but the "OR" logic can be implemented in different ways. 1. Use multiple SUMIFS to add up, such as =SUMIFS (total sales, region, "east") SUMIFS (total sales, region, "north"), the advantages are that they are simple and intuitive, but the conditions are complicated when there are many conditions; 2. Use arrays to combine SUM and SUMIFS, such as =SUM (SUMIFS (total sales, region, {"east", "north"})), the writing is concise, but some versions need to be Ctrl Shift Enter; 3. Use SUMPRODUCT to match logic to judge, such as =SUMPRODUCT ((region="east") (region="north"), total sales), which is highly flexible but not intuitive enough for novices and may be slightly slow under large data volume.

When using SUMIFS function in Excel, if you need to set multiple conditions for the same column (such as finding the sum of values ??in a certain range), many users will encounter confusion. Because the basic structure of SUMIFS is the relationship between "multiple conditions and", and multiple conditions in the same column are usually "or", this cannot be directly applied to the conventional writing method.

How to understand the limitations of SUMIFS?

SUMIFS itself is a "and" logic function, meaning that all conditions must be met at the same time. For example:

 =SUMIFS (total sales, region, "east", month, "January")

The meaning of this formula is: count the total sales of "the region is eastern and the month is January".

But if you want to count the sales of "the region is east or north", you can't simply add another condition. Because SUMIFS does not support "or" logic in the same column.

Solution 1: Use multiple SUMIFS to add up

This is the most commonly used and easy to understand method. You can write two SUMIFS separately and add the results:

 =SUMIFS (total sales, region, "East") SUMIFS (total sales, region, "North")

This method is suitable for situations where there are not many conditions. For example, if you want to check the sales sum of "apples, bananas, and oranges", you can write three SUMIFS to add up.

advantage:

• Simple and intuitive, easy to debug
• Good compatibility, suitable for various versions of Excel

shortcoming:

• There will be more verbose conditions
• A little higher maintenance cost

Solution 2: Use array SUM and SUMIFS to combine

You can also use array features to simplify writing, such as:

 =SUM(SUMIFS(total sales, region, {"East","North"}))

Here we use {"東部","北部"} to construct an array, telling Excel to calculate the sum of these two values ??separately, and then add them up with the outer SUM .

Note:

• This method looks concise, but in some older versions of Excel (such as 2016 and before) may require Ctrl Shift Enter to take effect
• If you put the condition in a cell (for example, A1:A2 is "East" or "North"), you cannot directly refer to this range, and you need to use other methods (for example, SUMPRODUCT )

Alternative: Use SUMPRODUCT with logical judgment

If you don't want to be restricted by SUMIFS structure, you can use SUMPRODUCT to achieve a more flexible combination:

 =SUMPRODUCT((region="East") (region="North"), total sales)

Here (區(qū)域="東部") (區(qū)域="北部") produces an array of 0 and 1 indicating which rows meet the criteria, and then multiply by sales and sum.

Advantages:

• More flexible, can handle "or" logic
• Support more complex condition combinations

Disadvantages:

• Not very intuitive for beginners
• It may be a little slower when the data volume is large

In general, for the "multiple conditions in the same column" sum, the most recommended method is to choose the appropriate method based on your Excel version and data complexity. If it is a small number of conditions, it is safest to add multiple SUMIFS ; if you pursue simplicity, you can also try array form or SUMPRODUCT .

Basically that's it.

The above is the detailed content of excel sumifs with multiple criteria in the same column. For more information, please follow other related articles on the PHP Chinese website!