Tom’s Tutorials For Excel: Conditional SUM of Alphanumeric Data
Here’s another crazy example — but it happens — when you are faced with making mathematical sense of numbers within alphanumeric data.
This example shows how to sum numbers that follow a certain set of characters, in this case numbers following “GT” in column A. I complicated things a little more, by inserting a space in some cells between letters and numbers, as a data entry person might have done.
The array formula in cell C2 is
This formula sums numbers, but you could also perform an
AVERAGE or other function; the concept would be the same.
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.