HELP WITH THIS FORMULA
 

What I am trying to do here is sum-up a bunch of cells but these cells have
"A" and the end of each value. For instance G8=4A, G38=6A, G98=9A etc.

I want to extract the values in the cells and add them up without the "A"s.
I was try to use the array formula below, but I am not getting the result I
want.

SUM(if(right({G8,G38,G68,G98,G128,G158,G188,G218,G 248,G278})="A",--SUBSTITUTE({G8,G38,G68,G98,G128,G158,G188,G218,G24 8,G278}),"A","") & "A",0)

Any ideas?

HELP WITH THIS FORMULA
 

This array formula did the totalling up:
=SUM(VALUE(SUBSTITUTE(A1:D1;"A";)))
but of course it doesn't pick separate cells but takes the entire
range 'A1:D1' instead of your example where the cells are 'cherry
picked'. This formula still works even if some of the cells have the
'A' or they don't.
If you still need to cherry pick the cells, then I'm afraid i wouldn't
know how to put them into an array...
Maybe the above helps anyway.
A.

On Jul 15, 6:29*pm, Ayo wrote:
What I am trying to do here is sum-up a bunch of cells but these cells have
"A" and the end of each value. For instance G8=4A, G38=6A, G98=9A etc.

I want to extract the values in the cells and add them up without the "A"s.
I was try to use the array formula below, but I am not getting the result I
want.

SUM(if(right({G8,G38,G68,G98,G128,G158,G188,G218,G 248,G278})="A",--SUBSTITU*TE({G8,G38,G68,G98,G128,G158,G188,G218,G2 48,G278}),"A","") & "A",0)

Any ideas?

HELP WITH THIS FORMULA
 

Hi,

I'd get rid of the A's in an anoother column using this formula

=SUBSTITUTE(G8,"A","")+0

Which uses column H and then add things up with this formula

=SUMPRODUCT(--(MOD(ROW(H8:H278)-ROW(H8),30)=0),H8:H278)

Mike

"Ayo" wrote:

What I am trying to do here is sum-up a bunch of cells but these cells have
"A" and the end of each value. For instance G8=4A, G38=6A, G98=9A etc.

I want to extract the values in the cells and add them up without the "A"s.
I was try to use the array formula below, but I am not getting the result I
want.

SUM(if(right({G8,G38,G68,G98,G128,G158,G188,G218,G 248,G278})="A",--SUBSTITUTE({G8,G38,G68,G98,G128,G158,G188,G218,G24 8,G278}),"A","") & "A",0)

Any ideas?