Choosing Numbers to be totaled

[David Harrison]

Hope anyone can help out there.

the situation is that in each cell i have (example of a
column)

1s 5
1s 8
1s 6
1s 2

What i want to do is add up all the numbers after 1s.

Remember that (1s 5) is all in one cell.

If it can be done then great but otherwise i will have to
seperate the cells.

 

[Bob Phillips]

David

Here is one way

=SUM(IF(NOT(ISERROR(VALUE(SUBSTITUTE(A1:A100,"1s","")))),VALUE(SUBSTITUTE(A1
:A100,"1s",""))),0)

It's an array formula, so enter with Ctrl;-Shift-Enter.

This strikes me as overly-clunky, and I can't believe it cannot be bettered.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[J.E. McGimpsey]

One way:

=SUMPRODUCT(--(LEFT(A1:A4,2)="1s"),--SUBSTITUTE(A1:A4,"1s",""))

 

[RagDyer]

Don't know how literal your question was, but you could add another column,
enter and copy down this formula, and simply enter a sum formula to the
bottom..
This will literally handle your column with up to 2 digits in each cell.

=--RIGHT(A1,2)
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================



Hope anyone can help out there.

the situation is that in each cell i have (example of a
column)

1s 5
1s 8
1s 6
1s 2

What i want to do is add up all the numbers after 1s.

Remember that (1s 5) is all in one cell.

If it can be done then great but otherwise i will have to
seperate the cells.

 

[Bob Phillips]

That's what I tried originally, problem is it fails if there are any blanks.

Bob

 

[J.E. McGimpsey]

Yep - I interpreted "each cell" as meaning no blanks, but I should
have made it explicit. Thanks!

 

[Bob Phillips]

I was hoping you had a way round that particular problem that I couldn't
see.

Bob

Yep - I interpreted "each cell" as meaning no blanks, but I should
have made it explicit. Thanks!

blanks.

 

[Ilan Rencus]

If your data is in, say, A1 to A4, try
=SUM(VALUE(MID(A1:A4,4,LEN(A1:A4)-3)))entered as an array formula with
Ctrl+Shift+Enter.
Ilan

 

[Aladin Akyurek]

If your data is in, say, A1 to A4, try
=SUM(VALUE(MID(A1:A4,4,LEN(A1:A4)-3)))entered as an array formula with
Ctrl+Shift+Enter.

What happens if one of the cells houses 2s 9 ?

 

[Ilan Rencus]

It makes no difference as long as the numbers starts at the fourth
place of the entry.
Ilan