counting duplicates error

excelNewbie22

Well-known Member
Joined
Aug 4, 2021
Messages
550
Office Version
  1. 365
Platform
  1. Windows
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
YZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAWAXAYAZBABBBCBDBEBFBGBHBIBJBK
112-62-102-272-323-103-273-326-276-3210-322-33-66-1010-2727-322-3-62-3-102-3-272-3-322-6-102-6-272-6-322-10-272-10-322-27-323-6-103-6-273-6-323-10-273-10-323-27-326-10-276-10-326-27-3210-27-323221
1210-2810-3110-3610-3724-3124-3624-3728-3628-3731-3710-2424-2828-3131-3636-3710-24-2810-24-3110-24-3610-24-3710-28-3110-28-3610-28-3710-31-3610-31-3710-36-3724-28-3124-28-3624-28-3724-31-3624-31-3724-36-3728-31-3628-31-3728-36-3731-36-3722
134-114-204-274-325-205-275-3211-2711-3220-324-55-1111-2020-2727-324-5-114-5-204-5-274-5-324-11-204-11-274-11-324-20-274-20-324-27-325-11-205-11-275-11-325-20-275-20-325-27-3211-20-2711-20-3211-27-3220-27-3200
1411-1611-2211-2411-3715-2215-2415-3716-2416-3722-3711-1515-1616-2222-2424-3711-15-1611-15-2211-15-2411-15-3711-16-2211-16-2411-16-3711-22-2411-22-3711-24-3715-16-2215-16-2415-16-3715-22-2415-22-3715-24-3716-22-2416-22-3716-24-3722-24-3711
152-92-192-202-357-197-207-359-209-3519-352-77-99-1919-2020-352-7-92-7-192-7-202-7-352-9-192-9-202-9-352-19-202-19-352-20-357-9-197-9-207-9-357-19-207-19-357-20-359-19-209-19-359-20-3519-20-3500
163-103-133-253-337-137-257-3310-2510-3313-333-77-1010-1313-2525-333-7-103-7-133-7-253-7-333-10-133-10-253-10-333-13-253-13-333-25-337-10-137-10-257-10-337-13-257-13-337-25-3310-13-2510-13-3310-25-3313-25-3333
173-243-283-303-347-287-307-3424-3024-3428-343-77-2424-2828-3030-343-7-243-7-283-7-303-7-343-24-283-24-303-24-343-28-303-28-343-30-347-24-287-24-307-24-347-28-307-28-347-30-3424-28-3024-28-3424-30-3428-30-3444
1000
Cell Formulas
RangeFormula
Y11:Y17Y11=C11&"-"&E11
Z11:Z17Z11=C11&"-"&F11
AA11:AA17AA11=C11&"-"&G11
AB11:AB17AB11=C11&"-"&H11
AC11:AC17AC11=D11&"-"&F11
AD11:AD17AD11=D11&"-"&G11
AE11:AE17AE11=D11&"-"&H11
AF11:AF17AF11=E11&"-"&G11
AG11:AG17AG11=E11&"-"&H11
AH11:AH17AH11=F11&"-"&H11
AI11:AM17AI11=C11&"-"&D11
AN11:AN17AN11=C11&"-"&D11&"-"&E11
AO11:AO17AO11=C11&"-"&D11&"-"&F11
AP11:AP17AP11=C11&"-"&D11&"-"&G11
AQ11:AQ17AQ11=C11&"-"&D11&"-"&H11
AR11:AR17AR11=C11&"-"&E11&"-"&F11
AS11:AS17AS11=C11&"-"&E11&"-"&G11
AT11:AT17AT11=C11&"-"&E11&"-"&H11
AU11:AU17AU11=C11&"-"&F11&"-"&G11
AV11:AV17AV11=C11&"-"&F11&"-"&H11
AW11:AW17AW11=C11&"-"&G11&"-"&H11
AX11:AX17AX11=D11&"-"&E11&"-"&F11
AY11:AY17AY11=D11&"-"&E11&"-"&G11
AZ11:AZ17AZ11=D11&"-"&E11&"-"&H11
BA11:BA17BA11=D11&"-"&F11&"-"&G11
BB11:BB17BB11=D11&"-"&F11&"-"&H11
BC11:BC17BC11=D11&"-"&G11&"-"&H11
BD11:BD17BD11=E11&"-"&F11&"-"&G11
BE11:BE17BE11=E11&"-"&F11&"-"&H11
BF11:BF17BF11=E11&"-"&G11&"-"&H11
BG11:BG17BG11=F11&"-"&G11&"-"&H11
BH11:BH17BH11=SUM(COUNTIF(Y12:BG17,Y11:BG11))
BI11:BI17BI11=SUM(--(COUNTIF(Y12:BG17,Y11:BG11)>0))
BJ11BJ11=COUNTIF(Y12:BG17,AC11)
BK11BK11=COUNTIF(Y12:BG17,AM11)
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

Forum statistics

Threads
1,226,466
Messages
6,191,197
Members
453,646
Latest member
SteenP

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top