Counting occurrences of initials with non-exact match option

[JoC]

I am trying to create a spreadsheet that keeps track of the number of times an employee uses a car park. We use our initials to book a parking spot. For example, the data below shows the reserved parking spaces for a week.

AF	JE	KS	SB	SB
JR	BD	AF - FMA training	TC	CC
ST from 2.30pm		BC	JE

If I use the COUNTIF function, it ignores occurrences in the cells where it does not match exactly, i.e. ST from 2.30 pm.

I think I need to use a LEN SUBSTITUTE combination, but I am not sure how to write the formula to look for a specific initial set and have it ignore any other words/symbols in the range of cells. This functionality is way above my ability to "figure it out" by using Google.

Any help would be greatly appreciated.

Jo

 

[kevin9999]

A simple Countif() with wildcards would do it.

Book1
	A	B	C	D	E
1	AF	JE	KS	SB	SB
2	JR	BD	AF - FMA training	TC	CC
3	ST from 2.30pm		BC	JE
4
5		Employee	# of times
6		AF	2
7		BC	1
8		BD	1
9		CC	1
10		JE	2
11		JR	1
12		KS	1
13		SB	2
14		ST	1
15		TC	1
Sheet2

Cell Formulas
Range	Formula
C6:C15	C6	=COUNTIF($A$1:$E$3,"*"&B6&"*")

 

[rlv01]

With and without the leading wildcard char.

Book2
	A	B	C	D	E
1	AF	JE	KS	SB	SB
2	JR	BD	AF - FMA training	TC	CC
3	ST from 2.30pm		BC	JE
4
5	Intials	Count 1	Count 2
6	AF	2	2
7	BC	1	1
8	BD	1	1
9	CC	1	1
10	JE	2	2
11	JR	1	1
12	KS	1	1
13	SB	2	2
14	ST	1	1
15	TC	1	1
16	FM	0	1
17	PM	0	1
18	MA	0	1
Sheet1

Cell Formulas
Range	Formula
B6:B18	B6	=COUNTIFS($A$1:$E$3,$A6 & "*" )
C6:C18	C6	=COUNTIFS($A$1:$E$3,"*" & $A6 & "*" )

 

[JoC]

Thank you for your quick reply. It worked perfectly barring one small issue. We have two people who share initials. We have an MB and then to differentiate the other person they use MBe. When I use the formula as it is, they are both showing as 12 when it should be 1 and 11. Is there a way to modify the wild card string without breaking the ignored extra info written in the cell?

 

[Peter_SSs]

Welcome to the MrExcel board!

Would this work for you?

22 09 28.xlsm
	A	B	C	D	E
1	AF	JE	KS	SB	SB
2	JR	AFa	AF - FMA training	TC	CC
3	ST from 2.30pm	AFa from 2pm	BC	JE
4
5		Employee	# of times
6		AF	2
7		BC	1
8		BD	0
9		CC	1
10		JE	2
11		JR	1
12		KS	1
13		SB	2
14		ST	1
15		TC	1
16		AFa	2
Count Initials

Cell Formulas
Range	Formula
C6:C16	C6	=COUNTIF(A$1:E$3,B6)+COUNTIF(A$1:E$3,B6&" *")