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

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

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

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

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

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

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

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

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