counting duplicates error

[excelNewbie22]

hi,
i'm trying to understand several points, what am i doing wrong?
was trying to count combination of numbers, of 2's and 3's,
i checked the results in several ways,
also with two very similar formulas
and
a:
get different results, why? what the difference between the two? bh11 and bi11
b:
why simple countif in bj11 return result of 2 instead of 1?
i get it see's result as partial, but then why bk11 return only 1 when is has in AW13 partial result as well?
marked the results in red
c:
how can i fix the formula to search the exact criteria with no partial results?

149.xlsm
	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI	BJ	BK
11	2-6	2-10	2-27	2-32	3-10	3-27	3-32	6-27	6-32	10-32	2-3	3-6	6-10	10-27	27-32	2-3-6	2-3-10	2-3-27	2-3-32	2-6-10	2-6-27	2-6-32	2-10-27	2-10-32	2-27-32	3-6-10	3-6-27	3-6-32	3-10-27	3-10-32	3-27-32	6-10-27	6-10-32	6-27-32	10-27-32	3	2	2	1
12	10-28	10-31	10-36	10-37	24-31	24-36	24-37	28-36	28-37	31-37	10-24	24-28	28-31	31-36	36-37	10-24-28	10-24-31	10-24-36	10-24-37	10-28-31	10-28-36	10-28-37	10-31-36	10-31-37	10-36-37	24-28-31	24-28-36	24-28-37	24-31-36	24-31-37	24-36-37	28-31-36	28-31-37	28-36-37	31-36-37	2	2
13	4-11	4-20	4-27	4-32	5-20	5-27	5-32	11-27	11-32	20-32	4-5	5-11	11-20	20-27	27-32	4-5-11	4-5-20	4-5-27	4-5-32	4-11-20	4-11-27	4-11-32	4-20-27	4-20-32	4-27-32	5-11-20	5-11-27	5-11-32	5-20-27	5-20-32	5-27-32	11-20-27	11-20-32	11-27-32	20-27-32	0	0
14	11-16	11-22	11-24	11-37	15-22	15-24	15-37	16-24	16-37	22-37	11-15	15-16	16-22	22-24	24-37	11-15-16	11-15-22	11-15-24	11-15-37	11-16-22	11-16-24	11-16-37	11-22-24	11-22-37	11-24-37	15-16-22	15-16-24	15-16-37	15-22-24	15-22-37	15-24-37	16-22-24	16-22-37	16-24-37	22-24-37	1	1
15	2-9	2-19	2-20	2-35	7-19	7-20	7-35	9-20	9-35	19-35	2-7	7-9	9-19	19-20	20-35	2-7-9	2-7-19	2-7-20	2-7-35	2-9-19	2-9-20	2-9-35	2-19-20	2-19-35	2-20-35	7-9-19	7-9-20	7-9-35	7-19-20	7-19-35	7-20-35	9-19-20	9-19-35	9-20-35	19-20-35	0	0
16	3-10	3-13	3-25	3-33	7-13	7-25	7-33	10-25	10-33	13-33	3-7	7-10	10-13	13-25	25-33	3-7-10	3-7-13	3-7-25	3-7-33	3-10-13	3-10-25	3-10-33	3-13-25	3-13-33	3-25-33	7-10-13	7-10-25	7-10-33	7-13-25	7-13-33	7-25-33	10-13-25	10-13-33	10-25-33	13-25-33	3	3
17	3-24	3-28	3-30	3-34	7-28	7-30	7-34	24-30	24-34	28-34	3-7	7-24	24-28	28-30	30-34	3-7-24	3-7-28	3-7-30	3-7-34	3-24-28	3-24-30	3-24-34	3-28-30	3-28-34	3-30-34	7-24-28	7-24-30	7-24-34	7-28-30	7-28-34	7-30-34	24-28-30	24-28-34	24-30-34	28-30-34	4	4
1000

Cell Formulas
Range	Formula
Y11:Y17	Y11	=C11&"-"&E11
Z11:Z17	Z11	=C11&"-"&F11
AA11:AA17	AA11	=C11&"-"&G11
AB11:AB17	AB11	=C11&"-"&H11
AC11:AC17	AC11	=D11&"-"&F11
AD11:AD17	AD11	=D11&"-"&G11
AE11:AE17	AE11	=D11&"-"&H11
AF11:AF17	AF11	=E11&"-"&G11
AG11:AG17	AG11	=E11&"-"&H11
AH11:AH17	AH11	=F11&"-"&H11
AI11:AM17	AI11	=C11&"-"&D11
AN11:AN17	AN11	=C11&"-"&D11&"-"&E11
AO11:AO17	AO11	=C11&"-"&D11&"-"&F11
AP11:AP17	AP11	=C11&"-"&D11&"-"&G11
AQ11:AQ17	AQ11	=C11&"-"&D11&"-"&H11
AR11:AR17	AR11	=C11&"-"&E11&"-"&F11
AS11:AS17	AS11	=C11&"-"&E11&"-"&G11
AT11:AT17	AT11	=C11&"-"&E11&"-"&H11
AU11:AU17	AU11	=C11&"-"&F11&"-"&G11
AV11:AV17	AV11	=C11&"-"&F11&"-"&H11
AW11:AW17	AW11	=C11&"-"&G11&"-"&H11
AX11:AX17	AX11	=D11&"-"&E11&"-"&F11
AY11:AY17	AY11	=D11&"-"&E11&"-"&G11
AZ11:AZ17	AZ11	=D11&"-"&E11&"-"&H11
BA11:BA17	BA11	=D11&"-"&F11&"-"&G11
BB11:BB17	BB11	=D11&"-"&F11&"-"&H11
BC11:BC17	BC11	=D11&"-"&G11&"-"&H11
BD11:BD17	BD11	=E11&"-"&F11&"-"&G11
BE11:BE17	BE11	=E11&"-"&F11&"-"&H11
BF11:BF17	BF11	=E11&"-"&G11&"-"&H11
BG11:BG17	BG11	=F11&"-"&G11&"-"&H11
BH11:BH17	BH11	=SUM(COUNTIF(Y12:BG17,Y11:BG11))
BI11:BI17	BI11	=SUM(--(COUNTIF(Y12:BG17,Y11:BG11)>0))
BJ11	BJ11	=COUNTIF(Y12:BG17,AC11)
BK11	BK11	=COUNTIF(Y12:BG17,AM11)

 

[jasonb75]

Does the delimiter have to be a dash / hyphen? Countif (and some other functions) automatically convert anything that looks like a date to a date so it's counting "3-10" as "3-10-25"

If you use an alternative character such as underscore to join the numbers together in columns Y:BG instead of a dash / hyphen then the count should work correctly.

If it absolutely must be a hyphen then we might have to start getting creative.

edit:-

Under a quick test, adding a leading space seems to resolve the issue.

Select Y11:BG17 (or extend down if your actual sheet has more rows.

Press Ctrl h

In the Find What: box, enter =
In the Replace With: box, enter =" "
Then click Replace All