Counting multiple letters in cells

[allunderme]

Hi,

I have a large list of policy numbers with different letters held within (eg MT/1/PD/101271A, 07BV/17345057, BL/1/ /635663A, MALZ32741) all have different formats and some contain /'s. They have all been manually added so each line differs slightly. I have a list of letter prefixes i wish to identify (BV, MT, MALZ etc) there are 11 in total.

Does anyone know how i can count these letter formats from this list of policy numbers?

Im scratching my head so any help would be really appreciated.

Allun

 

[pgc01]

Hi Allun

To count for ex. the ones that start with "BV", try:

=COUNTIF(A2:A1000,"BV*")

 

[pgc01]

... you can also refer to a cell.

Write "BV" in B1 and use:

=COUNTIF(A2:A1000,B1&"*")

 

[Pup Denab]

HI Allun
I would create a matrix, like below, the reason are as follows, depending on your list, we don't know if you have duplicates i.e. policy number which will return a true value (contain more than one of you keys), in my column E, it will tell you if their are found more than once, you would need to check these manually, if only found once, you could just count as requested in the small 2 column list below

<colgroup><col style="mso-width-source:userset;mso-width-alt:9508;width:195pt" width="260"> <col style="mso-width-source:userset;mso-width-alt:877;width:18pt" width="24"> <col style="mso-width-source:userset;mso-width-alt:950;width:20pt" width="26"> <col style="mso-width-source:userset;mso-width-alt:1499;width:31pt" width="41"> <col style="mso-width-source:userset;mso-width-alt:1060;width:22pt" width="29"> <col style="mso-width-source:userset;mso-width-alt:3510;width:72pt" width="96"> <col style="width:48pt" width="64"> <col style="mso-width-source:userset;mso-width-alt:1499;width:31pt" width="41"> <col style="mso-width-source:userset;mso-width-alt:512;width:11pt" width="14"> </colgroup><tbody>
[TD="width: 260"]

Regards Pup You can just copy and past the data above into a new spreadsheet, so you can see how it works, please get back to me if this doesn't resolve your issue

<colgroup><col style="mso-width-source:userset;mso-width-alt:4790;width:197pt" width="262"> <col style="mso-width-source:userset;mso-width-alt:3730;width:153pt" width="204"> <col style="mso-width-source:userset;mso-width-alt:3949;width:162pt" width="216"> <col style="mso-width-source:userset;mso-width-alt:3437;width:141pt" width="188"> <col style="mso-width-source:userset;mso-width-alt:1938;width:80pt" width="106"> </colgroup><tbody>
[TD="width: 204"]

MT/1/PD/101271A	=IFERROR(SEARCH($B$1,A2),"")	=IFERROR(SEARCH($C$1,A2,1),"")	=IFERROR(FIND($D$1,A2),"")	=COUNT(B2:D2)
07BV/17345057	=IFERROR(SEARCH($B$1,A3),"")	=IFERROR(SEARCH($C$1,A3,1),"")	=IFERROR(FIND($D$1,A3),"")	=COUNT(B3:D3)
BL/1/ /635663A	=IFERROR(SEARCH($B$1,A4),"")	=IFERROR(SEARCH($C$1,A4,1),"")	=IFERROR(FIND($D$1,A4),"")	=COUNT(B4:D4)
MALZ32741	=IFERROR(SEARCH($B$1,A5),"")	=IFERROR(SEARCH($C$1,A5,1),"")	=IFERROR(FIND($D$1,A5),"")	=COUNT(B5:D5)
MT/1/PD/101271A	=IFERROR(SEARCH($B$1,A6),"")	=IFERROR(SEARCH($C$1,A6,1),"")	=IFERROR(FIND($D$1,A6),"")	=COUNT(B6:D6)
BV	=COUNT(B1:B7)
MT	=COUNT(C1:C7)
MALZ	=COUNT(D1:D7)

<colgroup><col style="mso-width-source:userset;mso-width-alt:4790;width:197pt" width="262"> <col style="mso-width-source:userset;mso-width-alt:3730;width:153pt" width="204"> <col style="mso-width-source:userset;mso-width-alt:3949;width:162pt" width="216"> <col style="mso-width-source:userset;mso-width-alt:3437;width:141pt" width="188"> <col style="mso-width-source:userset;mso-width-alt:1938;width:80pt" width="106"> </colgroup><tbody>
[TD="width: 262"]Policy Numbers [/TD]
[TD="width: 204"]BV[/TD]
[TD="width: 216"]MT[/TD]
[TD="width: 188"]MALZ[/TD]
[TD="width: 106"]Count of none[/TD]

</tbody>

[/TD]
[TD="width: 216"][/TD]
[TD="width: 188"][/TD]
[TD="width: 106"][/TD]

</tbody>

[/TD]
[TD="width: 24"][/TD]
[TD="width: 26"][/TD]
[TD="width: 41"][/TD]
[TD="width: 29"][/TD]
[TD="width: 96"][/TD]
[TD="width: 64"][/TD]
[TD="width: 41"][/TD]
[TD="width: 14, align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

</tbody>

 

[allunderme]

thank you but do you know how i would count the lines containing "BV" where this is not at the begining of the cell?

These letter codes are all in different locations within the cell

 

[allunderme]

Thanks Pup

i will give it a go and get back to you

 

[pgc01]

If it is not at the beginning of the cell:

=COUNTIF(A2:A1000,"*BV*")

This will find BV anywhere in the text of the cells.

 

[hiker95]

allunderme,

How about:

Excel 2007
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Policy Numbers	*BV*	*MT*	*MALZ*	*4*	*5*	*6*	*7*	*8*	*9*	*10*	*11*		Codes	Totals
2	MT/1/PD/101271A	0	1	0	0	0	0	1	0	0	1	0		BV	1
3	07BV/17345057	1	0	0	1	1	0	1	0	0	0	0		MT	2
4	BL/1/ /635663A	0	0	0	0	1	1	0	0	0	0	0		MALZ	1
5	MALZ32741	0	0	1	1	0	0	1	0	0	0	0		4	2
6	MT/1/PD/101271A	0	1	0	0	0	0	1	0	0	1	0		5	2
7														6	1
8														7	4
9														8	0
10														9	0
11														10	2
12														11	0
13															15
14
Sheet1

Cell Formulas
Range	Formula
B2	=COUNTIF($A2,B$1)
O2	=SUM($B:$B)
O3	=SUM($C:$C)
O4	=SUM(D:D)
O5	=SUM(E:E)
O6	=SUM(F:F)
O7	=SUM(G:G)
O8	=SUM(H:H)
O9	=SUM(I:I)
O10	=SUM(J:J)
O11	=SUM(K:K)
O12	=SUM(L:L)
O13	=SUM(O2:O12)

Add the additional codes in range E1:L1, with leading and trailing * characters.

And, add the additional codes in range N5:N12 without the leading and trailing * characters.


The formula in cell B2, copied to range B2:L6:

=COUNTIF($A2,B$1)

 

[allunderme]

Thank you all for your assistance. Im still unable to complete my task but i wont be beaten

 

[Aladin Akyurek]

Hi,

I have a large list of policy numbers with different letters held within (eg MT/1/PD/101271A, 07BV/17345057, BL/1/ /635663A, MALZ32741) all have different formats and some contain /'s. They have all been manually added so each line differs slightly. I have a list of letter prefixes i wish to identify (BV, MT, MALZ etc) there are 11 in total.

Does anyone know how i can count these letter formats from this list of policy numbers?

Im scratching my head so any help would be really appreciated.

Allun

Given a sample like:
[TABLE="width: 89"]
<tbody>[TR]
[TD="class: xl63, width: 118, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]MT/1/PD/101271A
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"] 07BV/17345057
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"] BL/1/ /635663A
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"] MALZ32741
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]

what is the expected outcome?