I have the following: G308=4A, H308=4P, I308=3A and J308=2P and so on for
that row.
Now I want the following to happen: G338=4, H338=4, I338=7 (4+3, the number
part of G308 and the number part of I308) and J338=6 (4+2, the number part of
H308 and the number part of J308)
I got a formula that would do the additions for me, array-entered,

=$F5+SUM(IF(MOD(COLUMN($G$5:G$5),2)=MOD(COLUMN(G5) ,2),$G5:G5,0))

if I didn't have the As and Ps in row 308. But what I really need is
something similar that would take into account the As and Ps.

Any ideas. Thanks

"Ayo" wrote:
I have the following: G308=4A, H308=4P, I308=3A and J308=2P and so on for
that row.
Now I want the following to happen: G338=4, H338=4, I338=7 (4+3, the
number
part of G308 and the number part of I308) and J338=6 (4+2, the number part
of
H308 and the number part of J308)

If the number before the letter is always one digit, then:

G338: =--left(G308,1)
H338: =--left(H308,1)
I338: =G338 + left(I308,1)
J338: =H338 + left(J308,1)

Alternatively, if the number before the letter might have multiple digits,
but the right-hand text is always a single letter, then replace LEFT(G308,1)
with LEFT(G308,LEN(308)-1), and similarly for the other LEFT expressions.


I got a formula that would do the additions for me, array-entered,
=$F5+SUM(IF(MOD(COLUMN($G$5:G$5),2)=MOD(COLUMN(G5) ,2),$G5:G5,0))

if I didn't have the As and Ps in row 308. But what I really need is
something similar that would take into account the As and Ps.

I don't see how that formula relates to the first part of the question, so I
don't know how to fix it so that it accommodates the As and Ps.


----- original message -----

"Ayo" wrote in message
...
I have the following: G308=4A, H308=4P, I308=3A and J308=2P and so on for
that row.
Now I want the following to happen: G338=4, H338=4, I338=7 (4+3, the
number
part of G308 and the number part of I308) and J338=6 (4+2, the number part
of
H308 and the number part of J308)

I got a formula that would do the additions for me, array-entered,

=$F5+SUM(IF(MOD(COLUMN($G$5:G$5),2)=MOD(COLUMN(G5) ,2),$G5:G5,0))

if I didn't have the As and Ps in row 308. But what I really need is
something similar that would take into account the As and Ps.

Any ideas. Thanks

G308=4A, H308=4P, I308=3A and J308=2P
take into account the As and Ps.

Do the cells *always* contain a single digit followed by a letter? Are there
any empty cells in the range? Are there any formulas in the range that
return formula blanks ("")?

--
Biff
Microsoft Excel MVP


"Ayo" wrote in message
...
I have the following: G308=4A, H308=4P, I308=3A and J308=2P and so on for
that row.
Now I want the following to happen: G338=4, H338=4, I338=7 (4+3, the
number
part of G308 and the number part of I308) and J338=6 (4+2, the number part
of
H308 and the number part of J308)
I got a formula that would do the additions for me, array-entered,

=$F5+SUM(IF(MOD(COLUMN($G$5:G$5),2)=MOD(COLUMN(G5) ,2),$G5:G5,0))

if I didn't have the As and Ps in row 308. But what I really need is
something similar that would take into account the As and Ps.

Any ideas. Thanks

I would be some empty cells in the range and yes, it will only be A and P and
the cells could be any number of digits, i.e, it could be 24A or 124A, 3P or
344P. I am trying to do a cumulative addition based on whether the cell value
ends in A or P and also adding all the previous cells.

"T. Valko" wrote:

G308=4A, H308=4P, I308=3A and J308=2P
take into account the As and Ps.

Do the cells *always* contain a single digit followed by a letter? Are there
any empty cells in the range? Are there any formulas in the range that
return formula blanks ("")?

--
Biff
Microsoft Excel MVP


"Ayo" wrote in message
...
I have the following: G308=4A, H308=4P, I308=3A and J308=2P and so on for
that row.
Now I want the following to happen: G338=4, H338=4, I338=7 (4+3, the
number
part of G308 and the number part of I308) and J338=6 (4+2, the number part
of
H308 and the number part of J308)
I got a formula that would do the additions for me, array-entered,

=$F5+SUM(IF(MOD(COLUMN($G$5:G$5),2)=MOD(COLUMN(G5) ,2),$G5:G5,0))

if I didn't have the As and Ps in row 308. But what I really need is
something similar that would take into account the As and Ps.

Any ideas. Thanks

Assuming that *every* cell that contains a letter P or A *also* contains a
number.

Try one of these array formula** :

=SUM(IF(RIGHT(A1:H1)="P",--SUBSTITUTE(A1:H1,"P","")))

=SUM(IF(RIGHT(A1:H1)="P",--LEFT(A1:H1,LEN(A1:H1)-1)))

The difference in these formulas is that the first one assumes the letter in
the cell will *always* be an upper case letter. In the second formula it
doesn't matter what case the letter is.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Ayo" wrote in message
...
I would be some empty cells in the range and yes, it will only be A and P
and
the cells could be any number of digits, i.e, it could be 24A or 124A, 3P
or
344P. I am trying to do a cumulative addition based on whether the cell
value
ends in A or P and also adding all the previous cells.

"T. Valko" wrote:

G308=4A, H308=4P, I308=3A and J308=2P
take into account the As and Ps.

Do the cells *always* contain a single digit followed by a letter? Are
there
any empty cells in the range? Are there any formulas in the range that
return formula blanks ("")?

--
Biff
Microsoft Excel MVP


"Ayo" wrote in message
...
I have the following: G308=4A, H308=4P, I308=3A and J308=2P and so on
for
that row.
Now I want the following to happen: G338=4, H338=4, I338=7 (4+3, the
number
part of G308 and the number part of I308) and J338=6 (4+2, the number
part
of
H308 and the number part of J308)
I got a formula that would do the additions for me, array-entered,

=$F5+SUM(IF(MOD(COLUMN($G$5:G$5),2)=MOD(COLUMN(G5) ,2),$G5:G5,0))

if I didn't have the As and Ps in row 308. But what I really need is
something similar that would take into account the As and Ps.

Any ideas. Thanks

Thanks Valko. I think it works but the cell is only show TRUE. How do I get
it to show the result of the SUM(), which in this case is 16?

"T. Valko" wrote:

Assuming that *every* cell that contains a letter P or A *also* contains a
number.

Try one of these array formula** :

=SUM(IF(RIGHT(A1:H1)="P",--SUBSTITUTE(A1:H1,"P","")))

=SUM(IF(RIGHT(A1:H1)="P",--LEFT(A1:H1,LEN(A1:H1)-1)))

The difference in these formulas is that the first one assumes the letter in
the cell will *always* be an upper case letter. In the second formula it
doesn't matter what case the letter is.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Ayo" wrote in message
...
I would be some empty cells in the range and yes, it will only be A and P
and
the cells could be any number of digits, i.e, it could be 24A or 124A, 3P
or
344P. I am trying to do a cumulative addition based on whether the cell
value
ends in A or P and also adding all the previous cells.

"T. Valko" wrote:

G308=4A, H308=4P, I308=3A and J308=2P
take into account the As and Ps.

Do the cells *always* contain a single digit followed by a letter? Are
there
any empty cells in the range? Are there any formulas in the range that
return formula blanks ("")?

--
Biff
Microsoft Excel MVP


"Ayo" wrote in message
...
I have the following: G308=4A, H308=4P, I308=3A and J308=2P and so on
for
that row.
Now I want the following to happen: G338=4, H338=4, I338=7 (4+3, the
number
part of G308 and the number part of I308) and J338=6 (4+2, the number
part
of
H308 and the number part of J308)
I got a formula that would do the additions for me, array-entered,

=$F5+SUM(IF(MOD(COLUMN($G$5:G$5),2)=MOD(COLUMN(G5) ,2),$G5:G5,0))

if I didn't have the As and Ps in row 308. But what I really need is
something similar that would take into account the As and Ps.

Any ideas. Thanks

the cell is only show TRUE.

Hmmm...

The formulas I posted will *never* return TRUE.

Post the *exact* formula you tried.

--
Biff
Microsoft Excel MVP


"Ayo" wrote in message
...
Thanks Valko. I think it works but the cell is only show TRUE. How do I
get
it to show the result of the SUM(), which in this case is 16?

"T. Valko" wrote:

Assuming that *every* cell that contains a letter P or A *also* contains
a
number.

Try one of these array formula** :

=SUM(IF(RIGHT(A1:H1)="P",--SUBSTITUTE(A1:H1,"P","")))

=SUM(IF(RIGHT(A1:H1)="P",--LEFT(A1:H1,LEN(A1:H1)-1)))

The difference in these formulas is that the first one assumes the letter
in
the cell will *always* be an upper case letter. In the second formula it
doesn't matter what case the letter is.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Ayo" wrote in message
...
I would be some empty cells in the range and yes, it will only be A and
P
and
the cells could be any number of digits, i.e, it could be 24A or 124A,
3P
or
344P. I am trying to do a cumulative addition based on whether the cell
value
ends in A or P and also adding all the previous cells.

"T. Valko" wrote:

G308=4A, H308=4P, I308=3A and J308=2P
take into account the As and Ps.

Do the cells *always* contain a single digit followed by a letter? Are
there
any empty cells in the range? Are there any formulas in the range that
return formula blanks ("")?

--
Biff
Microsoft Excel MVP


"Ayo" wrote in message
...
I have the following: G308=4A, H308=4P, I308=3A and J308=2P and so on
for
that row.
Now I want the following to happen: G338=4, H338=4, I338=7 (4+3, the
number
part of G308 and the number part of I308) and J338=6 (4+2, the
number
part
of
H308 and the number part of J308)
I got a formula that would do the additions for me, array-entered,

=$F5+SUM(IF(MOD(COLUMN($G$5:G$5),2)=MOD(COLUMN(G5) ,2),$G5:G5,0))

if I didn't have the As and Ps in row 308. But what I really need is
something similar that would take into account the As and Ps.

Any ideas. Thanks

Thanks. I figured out what the problem was; I had two formulas in the same
cell. That was why I was getting that error.
Thank you so much.

"T. Valko" wrote:

the cell is only show TRUE.

Hmmm...

The formulas I posted will *never* return TRUE.

Post the *exact* formula you tried.

--
Biff
Microsoft Excel MVP


"Ayo" wrote in message
...
Thanks Valko. I think it works but the cell is only show TRUE. How do I
get
it to show the result of the SUM(), which in this case is 16?

"T. Valko" wrote:

Assuming that *every* cell that contains a letter P or A *also* contains
a
number.

Try one of these array formula** :

=SUM(IF(RIGHT(A1:H1)="P",--SUBSTITUTE(A1:H1,"P","")))

=SUM(IF(RIGHT(A1:H1)="P",--LEFT(A1:H1,LEN(A1:H1)-1)))

The difference in these formulas is that the first one assumes the letter
in
the cell will *always* be an upper case letter. In the second formula it
doesn't matter what case the letter is.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Ayo" wrote in message
...
I would be some empty cells in the range and yes, it will only be A and
P
and
the cells could be any number of digits, i.e, it could be 24A or 124A,
3P
or
344P. I am trying to do a cumulative addition based on whether the cell
value
ends in A or P and also adding all the previous cells.

"T. Valko" wrote:

G308=4A, H308=4P, I308=3A and J308=2P
take into account the As and Ps.

Do the cells *always* contain a single digit followed by a letter? Are
there
any empty cells in the range? Are there any formulas in the range that
return formula blanks ("")?

--
Biff
Microsoft Excel MVP


"Ayo" wrote in message
...
I have the following: G308=4A, H308=4P, I308=3A and J308=2P and so on
for
that row.
Now I want the following to happen: G338=4, H338=4, I338=7 (4+3, the
number
part of G308 and the number part of I308) and J338=6 (4+2, the
number
part
of
H308 and the number part of J308)
I got a formula that would do the additions for me, array-entered,

=$F5+SUM(IF(MOD(COLUMN($G$5:G$5),2)=MOD(COLUMN(G5) ,2),$G5:G5,0))

if I didn't have the As and Ps in row 308. But what I really need is
something similar that would take into account the As and Ps.

Any ideas. Thanks

Ok, good deal. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Ayo" wrote in message
...
Thanks. I figured out what the problem was; I had two formulas in the same
cell. That was why I was getting that error.
Thank you so much.

"T. Valko" wrote:

the cell is only show TRUE.

Hmmm...

The formulas I posted will *never* return TRUE.

Post the *exact* formula you tried.

--
Biff
Microsoft Excel MVP


"Ayo" wrote in message
...
Thanks Valko. I think it works but the cell is only show TRUE. How do I
get
it to show the result of the SUM(), which in this case is 16?

"T. Valko" wrote:

Assuming that *every* cell that contains a letter P or A *also*
contains
a
number.

Try one of these array formula** :

=SUM(IF(RIGHT(A1:H1)="P",--SUBSTITUTE(A1:H1,"P","")))

=SUM(IF(RIGHT(A1:H1)="P",--LEFT(A1:H1,LEN(A1:H1)-1)))

The difference in these formulas is that the first one assumes the
letter
in
the cell will *always* be an upper case letter. In the second formula
it
doesn't matter what case the letter is.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Ayo" wrote in message
...
I would be some empty cells in the range and yes, it will only be A
and
P
and
the cells could be any number of digits, i.e, it could be 24A or
124A,
3P
or
344P. I am trying to do a cumulative addition based on whether the
cell
value
ends in A or P and also adding all the previous cells.

"T. Valko" wrote:

G308=4A, H308=4P, I308=3A and J308=2P
take into account the As and Ps.

Do the cells *always* contain a single digit followed by a letter?
Are
there
any empty cells in the range? Are there any formulas in the range
that
return formula blanks ("")?

--
Biff
Microsoft Excel MVP


"Ayo" wrote in message
...
I have the following: G308=4A, H308=4P, I308=3A and J308=2P and so
on
for
that row.
Now I want the following to happen: G338=4, H338=4, I338=7 (4+3,
the
number
part of G308 and the number part of I308) and J338=6 (4+2, the
number
part
of
H308 and the number part of J308)
I got a formula that would do the additions for me,
array-entered,

=$F5+SUM(IF(MOD(COLUMN($G$5:G$5),2)=MOD(COLUMN(G5) ,2),$G5:G5,0))

if I didn't have the As and Ps in row 308. But what I really need
is
something similar that would take into account the As and Ps.

Any ideas. Thanks