# How to use the countif funtion to count a codes that contain cells with muliple codes.

1. ## How to use the countif funtion to count a codes that contain cells with muliple codes.

I have an excel spreadsheet with a column that contains muliple codes separated by ~. (IE. ~33~)

For instance,
Column A CONTAINS A SERIES OF CODES USED FOR IDENTIFYING TYPES OF DATA
A1= ~1~~22~~45~
A2= ~33~~133~~22~
A3= ~33~
A4= ~133~
A5= ~126~

Column B contains the average days pending
B1= 24
B2=25
B3=48
B4=37
B5=22

Does anyone know what function I can use to count how many times "33" appears in column A? When I use =COUNTIF(A:A,"~33~"), it only counts 1. When I use =COUNTIF(A:A,"*33*"), it counts 4.

Please help   Register To Reply

2. ## Re: How to use the countif funtion to count a codes that contain cells with muliple codes.

welcome to the forum. so it's supposed to return 2? try:
=COUNTIF(A:A,"*~~33~~*")

when you use tilde (~), it take the next symbol literally. that means if A1:A2 contains:
*A
BA

and i want to count those *A, i cannot use:
=COUNTIF(A:A,"*A")
that will be anything before "A".

i must use it with a tilde to take the asterisk literally.
=COUNTIF(A:A,"~*A")

if it's possible 1 single cell contains 2 "~33~", and you want it as 2 counts, then:
=SUMPRODUCT(--(LEN(A1:A10)-LEN(SUBSTITUTE(A1:A10,"~33~",""))))/LEN("~33~")

the last portion can of course be divided by 4 instead of LEN("~33~"). just in case you need it for other characters  Register To Reply