0

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.

Improve this question
1
  • 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.
    – yosh m
    Oct 1, 2016 at 18:40

1 Answer 1

Reset to default
1

If it is always one number on the right of the text that you need to sum you can use the following:

=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
A1:G6 is the range of the calendar even with empty cells before 1 and after 31

Improve this answer
1
  • 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)))
    – Scott Craner
    Oct 2, 2016 at 4:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.