Choosing Numbers to be totaled

  • Thread starter Thread starter David Harrison
  • Start date Start date
D

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.
 
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)
 
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.
 
That's what I tried originally, problem is it fails if there are any blanks.

Bob
 
Yep - I interpreted "each cell" as meaning no blanks, but I should
have made it explicit. Thanks!
 
I was hoping you had a way round that particular problem that I couldn't
see.

Bob

J.E. McGimpsey said:
Yep - I interpreted "each cell" as meaning no blanks, but I should
have made it explicit. Thanks!
Click to expand...
blanks.
 
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
 
Ilan Rencus said:
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.
Click to expand...

What happens if one of the cells houses 2s 9 ?
 
It makes no difference as long as the numbers starts at the fourth
place of the entry.
Ilan
 
Back
Top