Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 --- |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 --- |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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... |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting groups of exact numbers in a huge list (column) | Excel Discussion (Misc queries) | |||
Running Excel 2000 VBA Application on Excel 2003 | Excel Worksheet Functions | |||
FedEx tracking numbers in Excel | Excel Discussion (Misc queries) | |||
How do I change format of page numbers to letters in Excel? | Excel Discussion (Misc queries) | |||
Can Excel evaluate a series of numbers | Excel Discussion (Misc queries) |