Counting numbers with a letter attached in excel
 

Hi all,
Sheet 1 Column A1 to A10 contains user input of say

10S
5S
6A
7B
25C
7C

the letter after the number gives the number a unique identifier.

How do I count these up so they become a number only on Sheet 2 in a
column

Bats Total 7
Cricket Balls 32
Antenna 6
Seats 15

Thanks for your help

Counting numbers with a letter attached in excel
 

In B1 enter:
=RIGHT(A1,1) and copy down
In C1 enter:
=LEFT(A1,LEN(A1)-1)*1 and copy down

In A1 thru C6 you should see:
10S S 10
5S S 5
6A A 6
7B B 7
25C C 25
7C C 7

Then the sum formulae a

=SUMPRODUCT(--(B1:B6="A"),C1:C6)
=SUMPRODUCT(--(B1:B6="B"),C1:C6)
=SUMPRODUCT(--(B1:B6="S"),C1:C6)
=SUMPRODUCT(--(B1:B6="C"),C1:C6)



--
Gary''s Student
gsnu200701


"pano" wrote:

Hi all,
Sheet 1 Column A1 to A10 contains user input of say

10S
5S
6A
7B
25C
7C

the letter after the number gives the number a unique identifier.

How do I count these up so they become a number only on Sheet 2 in a
column

Bats Total 7
Cricket Balls 32
Antenna 6
Seats 15

Thanks for your help

Counting numbers with a letter attached in excel
 

One way ..

In Sheet2,

Assuming this is listed in A1 down,

Bats
Cricket Balls
Antenna
Seats

Then place in B1:
=SUMPRODUCT(--(RIGHT(Sheet1!$A$1:$A$6)=LEFT(A1)),--LEFT(Sheet1!$A$1:$A$6,LEN(Sheet1!$A$1:$A$6)-1))
Copy B1 down to return the required results
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"pano" wrote in message
oups.com...
Hi all,
Sheet 1 Column A1 to A10 contains user input of say

10S
5S
6A
7B
25C
7C

the letter after the number gives the number a unique identifier.

How do I count these up so they become a number only on Sheet 2 in a
column

Bats Total 7
Cricket Balls 32
Antenna 6
Seats 15

Thanks for your help

Counting numbers with a letter attached in excel
 

Thanks Gary, works perfectly and great for the quick reply now I can
finish this of.

Gary''s Student wrote:
In B1 enter:
=RIGHT(A1,1) and copy down
In C1 enter:
=LEFT(A1,LEN(A1)-1)*1 and copy down

In A1 thru C6 you should see:
10S S 10
5S S 5
6A A 6
7B B 7
25C C 25
7C C 7

Then the sum formulae a

=SUMPRODUCT(--(B1:B6="A"),C1:C6)
=SUMPRODUCT(--(B1:B6="B"),C1:C6)
=SUMPRODUCT(--(B1:B6="S"),C1:C6)
=SUMPRODUCT(--(B1:B6="C"),C1:C6)



--
Gary''s Student
gsnu200701


"pano" wrote:

Hi all,
Sheet 1 Column A1 to A10 contains user input of say

10S
5S
6A
7B
25C
7C

the letter after the number gives the number a unique identifier.

How do I count these up so they become a number only on Sheet 2 in a
column

Bats Total 7
Cricket Balls 32
Antenna 6
Seats 15

Thanks for your help

Counting numbers with a letter attached in excel
 

Max all I seem to get is 0 using your array??


Max wrote:
One way ..

In Sheet2,

Assuming this is listed in A1 down,

Bats
Cricket Balls
Antenna
Seats

Then place in B1:
=SUMPRODUCT(--(RIGHT(Sheet1!$A$1:$A$6)=LEFT(A1)),--LEFT(Sheet1!$A$1:$A$6,LEN(Sheet1!$A$1:$A$6)-1))
Copy B1 down to return the required results
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"pano" wrote in message
oups.com...
Hi all,
Sheet 1 Column A1 to A10 contains user input of say

10S
5S
6A
7B
25C
7C

the letter after the number gives the number a unique identifier.

How do I count these up so they become a number only on Sheet 2 in a
column

Bats Total 7
Cricket Balls 32
Antenna 6
Seats 15

Thanks for your help

Counting numbers with a letter attached in excel
 

Max all I seem to get is 0 using your array??


Max wrote:
One way ..

In Sheet2,

Assuming this is listed in A1 down,

Bats
Cricket Balls
Antenna
Seats

Then place in B1:
=SUMPRODUCT(--(RIGHT(Sheet1!$A$1:$A$6)=LEFT(A1)),--LEFT(Sheet1!$A$1:$A$6,LEN(Sheet1!$A$1:$A$6)-1))
Copy B1 down to return the required results
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"pano" wrote in message
oups.com...
Hi all,
Sheet 1 Column A1 to A10 contains user input of say

10S
5S
6A
7B
25C
7C

the letter after the number gives the number a unique identifier.

How do I count these up so they become a number only on Sheet 2 in a
column

Bats Total 7
Cricket Balls 32
Antenna 6
Seats 15

Thanks for your help

Counting numbers with a letter attached in excel
 

Hmm, I've just discovered, that if the cell A1 does not have an entry
(is Blank) #value comes up in all cells because the cell is empty is
there a way around this as some cells will be empty

Counting numbers with a letter attached in excel
 

That's an operating constraint <g. The error happens with Gary S's
suggestion too, so I'm surprised that you didn't comment on this in your
earlier response to GS. If GS's suggestion worked perfect for you, so will
mine.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"pano" wrote in message
oups.com...
Hmm, I've just discovered, that if the cell A1 does not have an entry
(is Blank) #value comes up in all cells because the cell is empty is
there a way around this as some cells will be empty

Counting numbers with a letter attached in excel
 

Max, I did'nt pick it up till I reliazed that some cells will be empty,
and of course as soon as the cell was empty #value error.

Oh well I thought somehow you could trap the error if it was an empty
cell and return a zero or nothing at all.

Thanks for your help anyway it's much appreciated....

Max wrote:
That's an operating constraint <g. The error happens with Gary S's
suggestion too, so I'm surprised that you didn't comment on this in your
earlier response to GS. If GS's suggestion worked perfect for you, so will
mine.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"pano" wrote in message
oups.com...
Hmm, I've just discovered, that if the cell A1 does not have an entry
(is Blank) #value comes up in all cells because the cell is empty is
there a way around this as some cells will be empty

Counting numbers with a letter attached in excel
 

Using Gary's solution, change it to read =IF(A1="","",RIGHT(A1,1)). Leave
the formula in C as is
--
Hth

Kassie Kasselman


"pano" wrote:

Max, I did'nt pick it up till I reliazed that some cells will be empty,
and of course as soon as the cell was empty #value error.

Oh well I thought somehow you could trap the error if it was an empty
cell and return a zero or nothing at all.

Thanks for your help anyway it's much appreciated....

Max wrote:
That's an operating constraint <g. The error happens with Gary S's
suggestion too, so I'm surprised that you didn't comment on this in your
earlier response to GS. If GS's suggestion worked perfect for you, so will
mine.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"pano" wrote in message
oups.com...
Hmm, I've just discovered, that if the cell A1 does not have an entry
(is Blank) #value comes up in all cells because the cell is empty is
there a way around this as some cells will be empty

Counting numbers with a letter attached in excel
 

"kassie" wrote:
Using Gary's solution,
change it to read =IF(A1="","",RIGHT(A1,1)).
Leave the formula in C as is

"pano" wrote:
.. trap the error if it was an empty cell
and return a zero or nothing at all.

Think it is precisely the formulas in col C which will trip up*, not the
ones in col B

*should there be source cells in col A which are either blank, or contain
only a single number, or are devoid of numeric (contain only alphas)

To trap all the possibilities which may happen in the source col A,

Try replacing Gary S's:
In C1 enter:
=LEFT(A1,LEN(A1)-1)*1 and copy down

with this in C1, copied down:
=IF(LEN(A1)<2,0,IF(ISERROR(LEFT(A1,LEN(A1)-1)*1),0,LEFT(A1,LEN(A1)-1)*1))

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Counting numbers with a letter attached in excel
 

Max just tried your formula and yes that works
with this in C1, copied down:
=IF(LEN(A1)<2,0,IF(ISERROR(LEFT(A1,LEN(A1)-1)*1),0,LEFT(A1,LEN(A1)-1)*1))

thanks so much
problem fixed...




Max wrote:
"kassie" wrote:
Using Gary's solution,
change it to read =IF(A1="","",RIGHT(A1,1)).
Leave the formula in C as is

"pano" wrote:
.. trap the error if it was an empty cell
and return a zero or nothing at all.

Think it is precisely the formulas in col C which will trip up*, not the
ones in col B

*should there be source cells in col A which are either blank, or contain
only a single number, or are devoid of numeric (contain only alphas)

To trap all the possibilities which may happen in the source col A,

Try replacing Gary S's:
In C1 enter:
=LEFT(A1,LEN(A1)-1)*1 and copy down

with this in C1, copied down:
=IF(LEN(A1)<2,0,IF(ISERROR(LEFT(A1,LEN(A1)-1)*1),0,LEFT(A1,LEN(A1)-1)*1))

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Counting numbers with a letter attached in excel
 

Glad it worked out ok for you !
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"pano" wrote in message
oups.com...
Max just tried your formula and yes that works
with this in C1, copied down:
=IF(LEN(A1)<2,0,IF(ISERROR(LEFT(A1,LEN(A1)-1)*1),0,LEFT(A1,LEN(A1)-1)*1))

thanks so much
problem fixed...

Counting numbers with a letter attached in excel
 

Thanks Gary, Max & Kassie Problem is fixed as below for anyone who is
interested.........

Problem:-
Sheet 1 Column A1 to A10 contains user input of say

10S
5S
6A
7B
25C
7C

the letter after the number gives the number a unique identifier.
However some cells may be blank !!(empty)
How do I count these up so they become a number only on Sheet 2 in a
column

Bats Total 7
Cricket Balls 32
Antenna 6
Seats 15

Answer which works for me from Gary & Max

in B1 enter:
=RIGHT(A1,1) and copy down
In C1 enter:
=IF(LEN(A1)<2,0,IF(ISERROR(LEFT(A1,LEN(A1)-1)*1),0,LEFT(A1,LEN(A1)-1)*1))

Then the sum formulae a

=SUMPRODUCT(--(B1:B6="A"),C1:C6)
=SUMPRODUCT(--(B1:B6="B"),C1:C6)
=SUMPRODUCT(--(B1:B6="S"),C1:C6)
=SUMPRODUCT(--(B1:B6="C"),C1:C6)

Thanks again for the help