Скачать или смотреть How to Use SUMIF in Excel to Sum Values Based on Name Subsets

Discover a unique solution to sum values in Excel based on whether names are subsets of other names. Perfect for data analysis!
---
This video is based on the question https://stackoverflow.com/q/70236031/ asked by the user 'Emjora' ( https://stackoverflow.com/u/4220217/ ) and on the answer https://stackoverflow.com/a/70236143/ provided by the user 'Emjora' ( https://stackoverflow.com/u/4220217/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: Excel - sum if name is subset of another

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Solving the Excel Challenge: Summing Values with Subset Names

Excel is a powerful tool for data analysis, but sometimes we run into specific challenges that may not have an obvious workaround. One such challenge arises when we wish to sum values based on a condition where names in one column are subsets of names in another. For anyone who has tackled this issue, the frustration is real. You're not alone if you've found yourself wondering how to achieve this in a straightforward manner!

Understanding the Problem

Imagine you have a table structured like this:

NameValueWhat I wantX11X-Y12X-Y-Z02X-V12In this table, the third column, What I want, shows the results we want to achieve. The goal is to calculate the sum in the Value column, but only for those Name entries that are subsets of the Name in the current row.

For example, when checking row 2 with the name 'X-Y', we want to sum the values of both 'X' and 'X-Y' because both of them are subsets of 'X-Y', yielding a total of 2.

Solution to the Problem

After some exploration, a formula can be used to solve this challenge effectively. Insert the following formula into cell C2 (the first cell of your What I want column):

[[See Video to Reveal this Text or Code Snippet]]

Breaking Down the Formula

To understand how this formula works, let's break it down into major components:

SUMPRODUCT Function: This function multiplies corresponding components in the given arrays and then returns the sum of those products. It's versatile and can handle criteria efficiently in this context.

$B$2:$B$5: This range specifies the values we want to sum. The dollar signs before row and column references indicate that these are absolute references, meaning they won't change when copied to other cells.

IFERROR(...): This is a safety measure to prevent errors when trying to find instances of names that do not exist in the range. It will return zero if any error arises instead of disrupting the formula.

FIND($A$2:$A$5; A2)>0: Here, we check if the names from the Name column (in the range $A$2:$A$5) can be found in the name from the current row (A2). If a name is found, it evaluates to TRUE; otherwise, it yields FALSE.

--(...): The double negative is used to convert TRUE/FALSE values into 1s and 0s respectively, which is necessary for the subsequent multiplication.

=1: Finally, we compare this to 1 to make sure that only TRUE (which is converted to 1) values are considered.

Applying the Solution Across the Table

Once you have the formula in C2, simply drag down the fill handle (small square at the bottom right of the cell) to apply the formula to the other cells in the column. This will automatically adjust for each row, summing the values based on whether the names are subsets as required.

Conclusion

Using this formula, you can now effectively sum values in Excel based on subset names. This is an excellent method for data analysis, especially when dealing with hierarchical or categorized data. Remember, Excel is a versatile tool, and it’s often just a matter of finding the right formula for your unique challenges.

By understanding this approach, you can tackle similar problems in the future with ease. Happy Excelling!