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?

 

[NBVC]

tRY:


Code:

 

[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!*

 

[NBVC]

Have you confirmed it with CTRL+SHIFT+ENTER keys.. to get the {
brackets..

Also, have you tried my formula.. which is more flexible, in that i
doesn't have to be an "A" at the end.. it accounts for any singl
character and it also allows you to change the "spacing" verticall
between values.. right now it is set to 30 for every 30th cell startin
at G8... but you can change that 30 to say 20 if you want to sum ever
20th cell, etc...

My formula, too, needs to be confirmed by holding the CTRL and SHIF
keys and hitting ENTER

 

[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