Count with criteria

[kumara_faith]

Hi,

I have the following table:

Book1
	A	B
1	Interactions	Policy Count
2	Customer came to make a complaint and make enquiry on policy 0012455 and A445587.	2
3	Policyholder made enquiry on policy 9988567 and S4444447.	2
4	Policyholder made enquiry on policy 9988567 and S4444447 and 5454545A	3
5	Policyholder made enquiry on policy 9988567/S4444447/5454545A	3
6	Customer came to make a complaint and make enquiry on policy 00124774and S449987.	2
Sheet1

In column A, I have the interactions with customer which also indicates the policy numbers of the customers. Since this interactions are entered manually by the service agent, there could be several possible variations as indicated in column A. Example in row no 6 is not a typo error but it is meant to show an example of the service agent entering the policynumber and the word "and" together as this could also be a possible variation.

In column B , I am trying to count the number of policy numbers mentioned in column A. The correct results are indicated in column B.

Is there a way to use a formula to derive this ? Appreciate all the help.

 

[Sanjeev1976]

You can try the below :

Book3
	A	B
1	Interactions	Policy Count
2	Customer came to make a complaint and make enquiry on policy 0012455 and A445587.	2
3	Policyholder made enquiry on policy 9988567 and S4444447.	2
4	Policyholder made enquiry on policy 9988567 and S4444447 and 5454545A	3
5	Policyholder made enquiry on policy 9988567/S4444447/5454545A	3
6	Customer came to make a complaint and make enquiry on policy 00124774and S449987.	2
Sheet1

Cell Formulas
Range	Formula
B2:B6	B2	=LET(p,SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXTAFTER(A2,"policy "),"and",","),".",""),"/",","),LEN(p)-LEN(SUBSTITUTE(p,",",""))+1)

 

[KOKOSEK]

Probably You won't be able to eliminate all possibilities which can be put 'incorrectly'.....

Possible solution as well:

Book1
	A	B	C
1	Interactions	expected	formula
2	Customer came to make a complaint and make enquiry on policy 0012455 and A445587.	2	2
3	Policyholder made enquiry on policy 9988567 and S4444447.	2	2
4	Policyholder made enquiry on policy 9988567 and S4444447 and 5454545A	3	3
5	Policyholder made enquiry on policy 9988567/S4444447/5454545A	3	3
6	Customer came to make a complaint and make enquiry on policy 00124774and S449987.	2	2
Sheet1

Cell Formulas
Range	Formula
C2:C6	C2	=COUNTA(LET(xR,TEXTSPLIT(SUBSTITUTE(SUBSTITUTE(A2,".",""),"/"," ")," ",,1),FILTER(xR,ISNUMBER(--(MID(xR,3,1))))))

 

[kumara_faith]

Hi Sanjeev and Koksek,

Thank you for the solution. Looking at the data, I realized, there is also another variation where the interaction with the customers could also have no policy numbers . Example as below:

Customer came for surrender. Managed to conserve.

Is there any way we could modify the formula to accommodate this scenario as well ? The correct result should be zero for policy count for this scenario.

 

[kumara_faith]

Hi,

Appreciate any help.

 

[myall_blues]

If you modify the solution provided by @Sanjeev1976 to

=IFERROR(LET(p,SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXTAFTER(A6,"policy "),"and",","),".",""),"/",","),LEN(p)-LEN(SUBSTITUTE(p,",",""))+1),0)

it should handle that situation.

 

[kumara_faith]

Hi myall,

Thank you. Just realized that if the word policy is not in the interaction documented by the service staff as below, the formula also returns 0 instead of counting the policy number. Example as below:

Customer came for surrender. Managed to conserve and make PW. 4545445A/5454545A

In the above scenario, the result should be 2 . Appreciate the help.

 

[Sanjeev1976]

Hello Kumara,

You need to provide all the different kind of scenarios to get the right help in 1 shot and cannot think of new scenarios every few minutes. The below captures your requirement :

Book4
	A	B
1	Interactions	Policy Count
2	Customer came to make a complaint and make enquiry on policy 0012455 and A445587.	2
3	Policyholder made enquiry on policy 9988567 and S4444447.	2
4	Policyholder made enquiry on policy 9988567 and S4444447 and 5454545A	3
5	Policyholder made enquiry on policy 9988567/S4444447/5454545A	3
6	Customer came to make a complaint and make enquiry on policy 00124774and S449987.	2
7	Customer came for surrender. Managed to conserve.	0
8	Customer came for surrender. Managed to conserve and make PW. 4545445A/5454545A	2
Sheet2

Cell Formulas
Range	Formula
B2:B8	B2	=IFERROR(LET(p,SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(IFERROR(TEXTAFTER(A2,"policy "),TEXTAFTER(A2,"PW. ")),"and",","),".",""),"/",","),LEN(p)-LEN(SUBSTITUTE(p,",",""))+1),0)

 

[kumara_faith]

Hi Sanjeev,

My apologies. I have combed through the database and here is all the possible scenarios:

Policy Count Formula.xlsx
	A	B	C
1	Scenario	Interactions	Policy Count
2	1	Customer came to make a complaint and make enquiry on policy 0012455 and A445587.	2
3	2	Policyholder made enquiry on policy 9988567 and S4444447.	2
4	3	Policyholder made enquiry on policy 9988567 and S4444447 and 5454545A	3
5	4	Policyholder made enquiry on policy 9988567/S4444447/5454545A	3
6	5	Customer came to make a complaint and make enquiry on policy 00124774and S449987.	2
7	6	Customer came for surrender. Managed to conserve and make PW. 4545445A/5454545A	2
8	7	Customer came for 3 policy to surrender. Managed to conserve 1 policy and another 2 PW	0
9	8	Customer came to surrender 3. Managed to conserve 1 policy and another 2 PW	0
10	9	Customer came to surrender 3. Managed to conserve 1 policy and another 2 PW/a444557/45545A	2
11	10	Customer came to surrender 3. Managed to conserve 1 and another 2 PW/a444557/45545A	2
Sheet1

Appreciate your help.

 

[Sanjeev1976]

You can use the below :

Book4
	A	B
1	Interactions	Policy Count
2	Customer came to make a complaint and make enquiry on policy 0012455 and A445587.	2
3	Policyholder made enquiry on policy 9988567 and S4444447.	2
4	Policyholder made enquiry on policy 9988567 and S4444447 and 5454545A	3
5	Policyholder made enquiry on policy 9988567/S4444447/5454545A	3
6	Customer came to make a complaint and make enquiry on policy 00124774and S449987.	2
7	Customer came for surrender. Managed to conserve and make PW. 4545445A/5454545A	2
8	Customer came for 3 policy to surrender. Managed to conserve 1 policy and another 2 PW	0
9	Customer came to surrender 3. Managed to conserve 1 policy and another 2 PW	0
10	Customer came to surrender 3. Managed to conserve 1 policy and another 2 PW/a444557/45545A	2
11	Customer came to surrender 3. Managed to conserve 1 and another 2 PW/a444557/45545A	2
Sheet2

Cell Formulas
Range	Formula
B2:B11	B2	=SUM(ISNUMBER(--(MID(TEXTSPLIT(SUBSTITUTE(SUBSTITUTE(A2,".",""),"/"," ")," ",,1),3,1)))*1)