Countifs ignoring duplicate dates in specific range

[-=NO=-]

Hello Guys;

I do appreciate your help to count the number of codes in each year ignoring the duplicates dates in this selected year

I used that formula but it's not ignoring duplicates

=COUNTIFS(Data!$B$2:$B$10000,B2,Data!$A$2:$A$10000,">=1/1/2012",Data!$A$2:$A$10000,"<1/1/2013")

[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 228"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]A (Date)[/TD]
[TD]B (Code)[/TD]
[/TR]
[TR]
[TD]29-06-09[/TD]
[TD]1NACHLRD[/TD]
[/TR]
[TR]
[TD]29-06-09[/TD]
[TD]1HYDRCAC[/TD]
[/TR]
[TR]
[TD]20-06-10[/TD]
[TD]1ACTCAC[/TD]
[/TR]
[TR]
[TD]29-06-12[/TD]
[TD]1MTLPRBNB[/TD]
[/TR]
[TR]
[TD]29-06-09[/TD]
[TD]1BNZYLALC[/TD]
[/TR]
[TR]
[TD]29-06-09[/TD]
[TD]1NAEDTA[/TD]
[/TR]
[TR]
[TD]29-06-09[/TD]
[TD]1PEG400[/TD]
[/TR]
[TR]
[TD]29-06-09[/TD]
[TD]1ETHNL96[/TD]
[/TR]
[TR]
[TD]29-06-09[/TD]
[TD]1DMNNTL[/TD]
[/TR]
[TR]
[TD]02-06-13[/TD]
[TD]1NACHLRD[/TD]
[/TR]
[TR]
[TD]29-06-09[/TD]
[TD]1NAACTTHD[/TD]
[/TR]
[TR]
[TD]29-06-09[/TD]
[TD]1ETHNL96[/TD]
[/TR]
[TR]
[TD]30-06-09[/TD]
[TD]2AMVAR05TR[/TD]
[/TR]
[TR]
[TD]12-07-12[/TD]
[TD]1MTLPRBNB[/TD]
[/TR]
[TR]
[TD]12-07-13[/TD]
[TD]1NAMTBSLF[/TD]
[/TR]
[TR]
[TD]16-07-14[/TD]
[TD]1NACHLRD[/TD]
[/TR]
[TR]
[TD]02-08-15[/TD]
[TD]1LCOVNCA[/TD]
[/TR]
[TR]
[TD]10-08-09[/TD]
[TD]2AMVAR05TR[/TD]
[/TR]
[TR]
[TD]10-08-09[/TD]
[TD]2AMVAR05TR[/TD]
[/TR]
[TR]
[TD]10-08-19[/TD]
[TD]2AMVAR05TR[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[DanteAmor]

What is the result you expect?
2-2
2-1
1-2
1-1
1-0
0-1


<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:91.25px;" /><col style="width:102.65px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >A (Date)</td><td >B (Code)</td><td >RESULT</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">29/06/2009</td><td >1NACHLRD</td><td style="text-align:right; ">0</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:right; ">29/06/2009</td><td >1HYDRCAC</td><td style="text-align:right; ">0</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:right; ">20/06/2010</td><td >1ACTCAC</td><td style="text-align:right; ">0</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:right; ">29/06/2012</td><td >1MTLPRBNB</td><td style="text-align:right; ">2</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="text-align:right; ">29/06/2009</td><td >1BNZYLALC</td><td style="text-align:right; ">0</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="text-align:right; ">29/06/2009</td><td >1NAEDTA</td><td style="text-align:right; ">0</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="text-align:right; ">29/06/2009</td><td >1PEG400</td><td style="text-align:right; ">0</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="text-align:right; ">29/06/2009</td><td >1ETHNL96</td><td style="text-align:right; ">0</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="text-align:right; ">29/06/2009</td><td >1DMNNTL</td><td style="text-align:right; ">0</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="text-align:right; ">02/06/2013</td><td >1NACHLRD</td><td style="text-align:right; ">0</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td style="text-align:right; ">29/06/2009</td><td >1NAACTTHD</td><td style="text-align:right; ">0</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td style="text-align:right; ">29/06/2009</td><td >1ETHNL96</td><td style="text-align:right; ">0</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td style="text-align:right; ">30/06/2009</td><td >2AMVAR05TR</td><td style="text-align:right; ">0</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td style="text-align:right; ">12/07/2012</td><td >1MTLPRBNB</td><td style="text-align:right; ">2</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td style="text-align:right; ">12/07/2013</td><td >1NAMTBSLF</td><td style="text-align:right; ">0</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >17</td><td style="text-align:right; ">16/07/2014</td><td >1NACHLRD</td><td style="text-align:right; ">0</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >18</td><td style="text-align:right; ">02/08/2015</td><td >1LCOVNCA</td><td style="text-align:right; ">0</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >19</td><td style="text-align:right; ">10/08/2009</td><td >2AMVAR05TR</td><td style="text-align:right; ">0</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >20</td><td style="text-align:right; ">10/08/2009</td><td >2AMVAR05TR</td><td style="text-align:right; ">0</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >21</td><td style="text-align:right; ">10/08/2019</td><td >2AMVAR05TR</td><td style="text-align:right; ">0</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >C2</td><td >=COUNTIFS(Data!$B$2:$B$10000,B2,Data!$A$2:$A$10000,">=1/1/2012",Data!$A$2:$A$10000,"<1/1/2013")</td></tr></table></td></tr></table>

 

[-=NO=-]

Thanks Dante

If we get that code "2AMVAR05TR" as an example, so i expect the results as below in red

What is the result you expect?
2-2
2-1
1-2
1-1
1-0
0-1

	A	B	C
A (Date)	B (Code)	RESULT
1NACHLRD
1HYDRCAC
1ACTCAC
1MTLPRBNB
1BNZYLALC
1NAEDTA
1PEG400
1ETHNL96
1DMNNTL
1NACHLRD
1NAACTTHD
1ETHNL96
2AMVAR05TR
1MTLPRBNB
1NAMTBSLF
1NACHLRD
1LCOVNCA
2AMVAR05TR
2AMVAR05TR
2AMVAR05TR

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:91.25px;"><col style="width:102.65px;"><col style="width:76.04px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]
[TD="align: right"]29/06/2009[/TD]

[TD="align: right"]0[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]
[TD="align: right"]29/06/2009[/TD]

[TD="align: right"]0[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]
[TD="align: right"]20/06/2010[/TD]

[TD="align: right"]0[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]
[TD="align: right"]29/06/2012[/TD]

[TD="align: right"]2[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]
[TD="align: right"]29/06/2009[/TD]

[TD="align: right"]0[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]7[/TD]
[TD="align: right"]29/06/2009[/TD]

[TD="align: right"]0[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]8[/TD]
[TD="align: right"]29/06/2009[/TD]

[TD="align: right"]0[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]9[/TD]
[TD="align: right"]29/06/2009[/TD]

[TD="align: right"]0[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]10[/TD]
[TD="align: right"]29/06/2009[/TD]

[TD="align: right"]0[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]11[/TD]
[TD="align: right"]02/06/2013[/TD]

[TD="align: right"]0[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]12[/TD]
[TD="align: right"]29/06/2009[/TD]

[TD="align: right"]0[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]13[/TD]
[TD="align: right"]29/06/2009[/TD]

[TD="align: right"]0[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]14[/TD]
[TD="align: right"]30/06/2009[/TD]

[TD="align: right"] 2 [/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]15[/TD]
[TD="align: right"]12/07/2012[/TD]

[TD="align: right"]2[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]16[/TD]
[TD="align: right"]12/07/2013[/TD]

[TD="align: right"]0[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]17[/TD]
[TD="align: right"]16/07/2014[/TD]

[TD="align: right"]0[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]18[/TD]
[TD="align: right"]02/08/2015[/TD]

[TD="align: right"]0[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]19[/TD]
[TD="align: right"]10/08/2009[/TD]

[TD="align: right"] 2 [/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]20[/TD]
[TD="align: right"]10/08/2009[/TD]

[TD="align: right"][/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]21[/TD]
[TD="align: right"]10/08/2019[/TD]

[TD="align: right"] 1 [/TD]

</tbody>

Cell Formula C2 =COUNTIFS(Data!$B$2:$B$10000,B2,Data!$A$2:$A$10000,">=1/1/2012",Data!$A$2:$A$10000,"<1/1/2013") <tbody> </tbody>

<tbody>
</tbody>

 

[DanteAmor]

Thanks Dante

If we get that code "2AMVAR05TR" as an example, so i expect the results as below in red

I do not find logic in your answer, you could explain why those results.
Note, I suppose it is typo error, in the formula of your example you have 2012 and in the selected examples you have 2009.

 

[-=NO=-]

Sorry Dante for mis understanding

I meant that the formula that i want will represent these results.
Please ignore my formula, it represents a false values

 

[Aryatect]

Hi,

I have put the formula for count if without dates, please feel free to modify that part, hoping rest works per your requirement:

Book1
	A	B	C
1	A (Date)	B (Code)	Count
2	6/29/2009	1NACHLRD	1
3	6/29/2009	1HYDRCAC	1
4	6/20/2010	1ACTCAC	1
5	6/29/2012	1MTLPRBNB	1
6	6/29/2009	1BNZYLALC	1
7	6/29/2009	1NAEDTA	1
8	6/29/2009	1PEG400	1
9	6/29/2009	1ETHNL96	2
10	6/29/2009	1DMNNTL	1
11	6/2/2013	1NACHLRD	1
12	6/29/2009	1NAACTTHD	1
13	6/29/2009	1ETHNL96
14	6/30/2009	2AMVAR05TR	1
15	7/12/2012	1MTLPRBNB	1
16	7/12/2013	1NAMTBSLF	1
17	7/16/2014	1NACHLRD	1
18	7/12/2012	1LCOVNCA	1
19	8/10/2009	2AMVAR05TR	2
20	8/10/2009	2AMVAR05TR
21	8/10/2019	2AMVAR05TR	1
Sheet4

Cell Formulas
Range	Formula
C2	=IF(SUMPRODUCT(--($A$1:A1&$B$1:B1=A2&B2))=0,COUNTIFS($A$2:$A$21,A2,$B$2:$B$21,$B2),"")

 

[DanteAmor]

Sorry Dante for mis understanding

I meant that the formula that i want will represent these results.
Please ignore my formula, it represents a false values

I understand about the formula.
But I was referring to your results in red.
You have years of 2009 and 2019.

 

[-=NO=-]

Thanks Aryatect

But I need formula to count depending on year (2012, 2013, etc.)

 

[-=NO=-]

OK Dante.

I have two sheets, the second is the target sheet, what i need is a formula in each cell to count the no. of code that repeated in specific year such as 2012

[TABLE="width: 500"]
<tbody>[TR]
[TD]Code[/TD]
[TD]2012[/TD]
[TD]2013[/TD]
[TD]2014[/TD]
[TD]2015[/TD]
[/TR]
[TR]
[TD]1DCTXLTRHD[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]1DXRBCNHCL[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]1ETPSD[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]

the first one, is containing the data

[TABLE="width: 500"]
<tbody>[TR]
[TD]Code[/TD]
[TD]Date[/TD]
[/TR]
[TR]
[TD]1DXRBCNHCL[/TD]
[TD]2/1/2013[/TD]
[/TR]
[TR]
[TD]1DCTXLTRHD[/TD]
[TD]1/5/2012[/TD]
[/TR]
[TR]
[TD]1DXRBCNHCL[/TD]
[TD]2/1/2012[/TD]
[/TR]
[TR]
[TD]1ETPSD[/TD]
[TD]10/7/2015[/TD]
[/TR]
[TR]
[TD]1DCTXLTRHD[/TD]
[TD]1/5/2012[/TD]
[/TR]
[TR]
[TD]1ETPSD[/TD]
[TD]2/1/2012[/TD]
[/TR]
[TR]
[TD]1ETPSD[/TD]
[TD]2/1/2012[/TD]
[/TR]
[TR]
[TD]1DXRBCNHCL[/TD]
[TD]2/1/2015[/TD]
[/TR]
[TR]
[TD]1DCTXLTRHD[/TD]
[TD]1/9/2015[/TD]
[/TR]
[TR]
[TD]1DXRBCNHCL[/TD]
[TD]22/10/2015[/TD]
[/TR]
</tbody>[/TABLE]

I hope this is clear now, and thanks again

 

[MARZIOTULLIO]

[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]Code[/TD]
[TD]Date[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]1DXRBCNHCL[/TD]
[TD]

2/1/2013​

[/TD]
[TD][/TD]
[TD]Sheet1[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]1DCTXLTRHD[/TD]
[TD]

1/5/2012​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]1DXRBCNHCL[/TD]
[TD]

2/1/2012​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]1ETPSD[/TD]
[TD]

10/7/2015​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]1DCTXLTRHD[/TD]
[TD]

1/5/2012​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD]1ETPSD[/TD]
[TD]

2/1/2012​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

8​

[/TD]
[TD]1ETPSD[/TD]
[TD]

2/1/2012​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

9​

[/TD]
[TD]1DXRBCNHCL[/TD]
[TD]

2/1/2015​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

10​

[/TD]
[TD]1DCTXLTRHD[/TD]
[TD]

1/9/2015​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

11​

[/TD]
[TD]1DXRBCNHCL[/TD]
[TD]

10/22/2015​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[TD]

F​

[/TD]
[TD]

G​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]Code[/TD]
[TD]

2012​

[/TD]
[TD]

2013​

[/TD]
[TD]

2014​

[/TD]
[TD]

2015​

[/TD]
[TD][/TD]
[TD]Sheet 2[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]1DCTXLTRHD[/TD]
[TD]

1​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]1DXRBCNHCL[/TD]
[TD]

1​

[/TD]
[TD]

1​

[/TD]
[TD]

0​

[/TD]
[TD]

2​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]1ETPSD[/TD]
[TD]

1​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


B2=IF(AND(Sheet2!$A2<>"",Sheet2!B$1<>""),SUM(IF(FREQUENCY(IF(Sheet1!$A$2:$A$11=Sheet2!$A2,IF(YEAR(Sheet1!$B$2:$B$11)=Sheet2!B$1,Sheet1!$B$2:$B$11)),Sheet1!$B$2:$B$11),1)),"")


Control+Shift+Enter


copy across and down

for hide 0

custom format [=0]""