Counting multiple initials in 1 cell

[gixered]

Hi everyone,

Really useful forum, have used your resources many times, but 1st time posting

I have put together an excel sheet to calculate human resources
The idea being i put each person's initials in a cell and use

+ COUNTIF(K3:K121, "each person initials")
e.g.
=COUNTIF(K3:K121, "AA") + COUNTIF(K3:K121, "AG") + COUNTIF(K3:K121, "AP") + COUNTIF(K3:K121, "DK") + COUNTIF(K3:K121, "EM") + COUNTIF(K3:K121, "LA") + COUNTIF(K3:K121, "MP") + COUNTIF(K3:K121, "MS")

And so on for every person
This is long-winded, but it works great

My problem is that if I add 2 sets of initials into 1 cell, not only does it not count the extra initials, it deduces the cells count
i.e.
Cell K4
AA AG

How do I count 2 sets of initials in 1 cell, please?

Cheers
Mark

 

[jasonb75]

Welcome to Mr Excel, Mark

There are a few easier ways to do what you're attempting that writing such a long formula. As long as it is always 2 initials, separated by a space when there are multiple sets in one cell then the easiest way would be to have a separate list of initials elsewhere in the sheet (or in another hidden sheet, entirely your choice) then use that list as a reference as below.

As with many things that we come across on the forum, if there are differences between the example provided and the actual format of your data then a different approach may be needed but hopefully this will get you going.

Book1
	K	L	M
1	List to count		List of each persons initials
2
3	AA		AA
4	EM		AK
5	AK		EM
6	MP EM		MP
7
8
9	Count result	5
Sheet1

Cell Formulas
Range	Formula
L9	L9	=SUM(COUNTIF($K$3:$K$6,"*"&$M$3:$M$6&"*"))

 

[Peter_SSs]

Welcome to the MrExcel board!

If you are just trying to count the number of peoples initials (no matter how many initials a person has) then here are a couple of other options

22 09 13.xlsm
	K	L	M
1	List to count
2
3	AA
4	EM EJMW
5	AK
6	MP EM ABC DE
7
8
9	Count result	8	If you have latest TEXT functions
10		8	If not
Count Initials

Cell Formulas
Range	Formula
L9	L9	=COLUMNS(TEXTSPLIT(TEXTJOIN(" ",1,K3:K6)," "))
L10	L10	=LET(s,TEXTJOIN(" ",1,K3:K6),LEN(s)-LEN(SUBSTITUTE(s," ",""))+1)

If neither of us has solved your problem then please consider giving us some dummy (but representative) sample data and expected results with XL2BB and explain agin in relation to that sample data