Tom’s Tutorials For Excel: Fuzzy Matching With Partial Strings For Sum or Average
When you have a mish-mash of alphanumeric strings in a list, here’s how you can sum or average their corresponding numbers based on a partial string criterion.
The picture shows two examples with the criterion in cell B4.
The example on the left evaluates each item in column B for the presence of “354” and sums for column C. The cells that match are B13, B16, and B19, so their corresponding numbers in cells C13, C16, and C19 sum to 168 in blue-colored cell C4, with array formula
The example on the right evaluates each item in column B for the presence of case insensitive “ATLAS” and averages for column C. The cells that match are B7, B8, B14, B15, and B19, with numbers in C7, C8, C14, C15, C19 averaged in pink-colored cell C4, by array formula
Recall, array formulas are applied to a cell by simultaneously pressing the Ctrl+Shift+Enter keys, not just Enter. The curly braces are not typed in by you; Excel places them automatically when the array formula is properly installed.