Help with this formula

[Ayo]

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,G248,G278})="A",--SUBSTITUTE({G8,G38,G68,G98,G128,G158,G188,G218,G248,G278}),"A","") & "A",0)

Any ideas?

 

[Luke M]

Try this array* formula:

=SUM(IF((ROW(G8:G278)={8,38,68,98,128,158,188,218,248,278})*(RIGHT(G8:G278,1)="A"),VALUE(SUBSTITUTE(G8:G278,"A","")),0))

*Confirm with Ctrl+Shift+Enter, not just Enter.

 

[Ayo]

I am getting #VALUE! error.

Try this array* formula:

=SUM(IF((ROW(G8:G278)={8,38,68,98,128,158,188,218,248,278})*(RIGHT(G8:G278,1)="A"),VALUE(SUBSTITUTE(G8:G278,"A","")),0))

*Confirm with Ctrl+Shift+Enter, not just Enter.

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*

 

[Ayo]

Thanks Luke. It works great.

Try this array* formula:

=SUM(IF((ROW(G8:G278)={8,38,68,98,128,158,188,218,248,278})*(RIGHT(G8:G278,1)="A"),VALUE(SUBSTITUTE(G8:G278,"A","")),0))

*Confirm with Ctrl+Shift+Enter, not just Enter.

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*

 

[Ayo]

Thanks NBVC. Yours works better. I didn't have to manually go into the
formula to make changes on every row that I needed to use the formula on.
Great thanks.
Ayo