Counting numbers with a letter attached in excel

[pano]

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

 

[Gary''s Student]

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 are:

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

 

[Max]

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

 

[pano]

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

 

[pano]

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

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

 

[pano]

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

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

 

[pano]

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

 

[Max]

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.

 

[pano]

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....

 

[kassie]

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

 

[Max]

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

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))

 

[pano]

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]

Glad it worked out ok for you !

 

[pano]

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 are:

=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