I have a range of cells like this (from a calendar).

``````                    1   2
3   4   5   SP4 7   8   9
10  11  12  SP4 14  15  16
17  18  19  20  21  22  23
24  25  26  27  28  29  30
31
``````

I want to add the numeric values from the alphanumeric cells ONLY, i.e. the 2 "SP4" cells, to equal 8. I have tried different array formulas, but can't figure out how to only select the 2 alphanumeric cells and add the numbers. Thanks ahead.

• can you make your question a little clearer? Maybe paste a screenshot of the Excel table in question? And what do you mean by "select"? You want a formula that identifies arbitrary text strings as containing numbers and then extract the numbers and add them up? Please explicate a bit further. And focus on the actual problem you want to solve rather than the method you think might solve it. Oct 1, 2016 at 18:40

`=SUMPRODUCT(--ISTEXT(A1:G6)*1*IF(ISTEXT(A1:G6),RIGHT(A1:G6,1),0))`
It is an array formula you have to press together `Ctrl` + `Shift` + `Enter`
• If you are going to make it an array type formula needing the Ctrl-Shift Enter, You only need the IF() inside a Sum : `=SUM(IF(ISTEXT(A1:G6),--RIGHT(A1:G6,1)))` Oct 2, 2016 at 4:38