Non adjacent cells in excel using INDIRECT function

0
votes

I am trying to create a formula to find only alpha numeric number in the cells and ignore the rest of the numbers and sum the values of the alpha numeric numbers alone. the formula I tired is working if I choose a range of cells. But I want to apply this only to alternate cells so I used INDIRECT function. I could see the indirect function is not grepping the values in the cell hence it fails. Can someone help me on this.

enter image description here

Attached, the sample excel pattern used

Formula working for continuous cells Ctrl+Shift+Enter: 

=SUM(IFERROR(VALUE(LEFT(A2:F2,LEN(A2:F2)-1)),0))

Formula not working for non adjacent cells:

=SUM(IFERROR(VALUE(LEFT(INDIRECT({"A2","C2","E2"}),LEN(INDIRECT({"A2","C2","E2"}))-1)),0))

In this I could see INDIRECT({"A2","C2","E2"}) itself is not returning the value of A2,C2,E2.

1
excel
If Your after alpha numeric only, why is A2 part of the formula? Just to make sure i understand, its still just the alpha numeric you wanna sum? - krib

1 Answers

0
votes

Try this array-entered formula:

{=SUM(IFERROR(VALUE(LEFT(T(OFFSET(A2,0,{0,2,4},1,1)),LEN(T(OFFSET(A2,0,{0,2,4},1,1)))-1)),0))}