Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Regression Leverage Formula (Jerry W. Lewis or Mike Middleton)already have DFITS formula | Excel Worksheet Functions | |||
copy formula down a column and have cell references change within formula | New Users to Excel | |||
Formula expected end of statement error, typing formula into cell as part of VBA macro | Excel Programming | |||
Cell doesn't show formula result - it shows formula (CTRL + ' doe. | Excel Worksheet Functions | |||
Commenting custom formula fields/formula on formula editor | Excel Programming |