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?

tRY:


Code:
--------------------
=SUM(IF(ISNUMBER(LEFT(G8:G278,LEN(G8:G278)-1)+0),IF((MOD(ROW(G8:G278)-ROW(G8),30)=0)*(LEFT(G8:G278,LEN(G8:G278)-1)+0),LEFT(G8:G278,LEN(G8:G278)-1)+0)))
--------------------


Confirmed with CTRL+SHIFT+ENTER not just ENTER


--
NBVC

Where there is a will there are many ways.
'The Code Cage' (http;//www.thecodecage.com)
------------------------------------------------------------------------
NBVC's Profile: http://www.thecodecage.com/forumz/member.php?userid=74
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=116324

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" 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?

I am getting #VALUE! error.

"Luke M" wrote:

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" 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?

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 it
doesn't have to be an "A" at the end.. it accounts for any single
character and it also allows you to change the "spacing" vertically
between values.. right now it is set to 30 for every 30th cell starting
at G8... but you can change that 30 to say 20 if you want to sum every
20th cell, etc...

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


--
NBVC

Where there is a will there are many ways.
'The Code Cage' (http;//www.thecodecage.com)
------------------------------------------------------------------------
NBVC's Profile: http://www.thecodecage.com/forumz/member.php?userid=74
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=116324

Thanks Luke. It works great.

"Luke M" wrote:

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" 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?

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

"NBVC" wrote:


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 it
doesn't have to be an "A" at the end.. it accounts for any single
character and it also allows you to change the "spacing" vertically
between values.. right now it is set to 30 for every 30th cell starting
at G8... but you can change that 30 to say 20 if you want to sum every
20th cell, etc...

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


--
NBVC

Where there is a will there are many ways.
'The Code Cage' (http;//www.thecodecage.com)
------------------------------------------------------------------------
NBVC's Profile: http://www.thecodecage.com/forumz/member.php?userid=74
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=116324