Countifs ignoring duplicate dates in specific range

-=NO=- · Jun 27, 2019

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

Code:

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

A (Date)	B (Code)
29-06-09	1NACHLRD
29-06-09	1HYDRCAC
20-06-10	1ACTCAC
29-06-12	1MTLPRBNB
29-06-09	1BNZYLALC
29-06-09	1NAEDTA
29-06-09	1PEG400
29-06-09	1ETHNL96
29-06-09	1DMNNTL
02-06-13	1NACHLRD
29-06-09	1NAACTTHD
29-06-09	1ETHNL96
30-06-09	2AMVAR05TR
12-07-12	1MTLPRBNB
12-07-13	1NAMTBSLF
16-07-14	1NACHLRD
02-08-15	1LCOVNCA
10-08-09	2AMVAR05TR
10-08-09	2AMVAR05TR
10-08-19	2AMVAR05TR

DanteAmor · Jun 27, 2019

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=- · Jun 28, 2019

Thanks Dante

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

DanteAmor said:
What is the result you expect?
2-2
2-1
1-2
1-1
1-0
0-1

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

<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>
</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 · Jun 28, 2019

-=NO=- said:
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=- · Jun 28, 2019

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 · Jun 28, 2019

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 · Jun 28, 2019

-=NO=- said:
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=- · Jun 29, 2019

Thanks Aryatect

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

-=NO=- · Jun 29, 2019

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

Code	2012	2013	2014	2015
1DCTXLTRHD	1			1
1DXRBCNHCL	1	1		2
1ETPSD	1			1

the first one, is containing the data

Code	Date
1DXRBCNHCL	2/1/2013
1DCTXLTRHD	1/5/2012
1DXRBCNHCL	2/1/2012
1ETPSD	10/7/2015
1DCTXLTRHD	1/5/2012
1ETPSD	2/1/2012
1ETPSD	2/1/2012
1DXRBCNHCL	2/1/2015
1DCTXLTRHD	1/9/2015
1DXRBCNHCL	22/10/2015

I hope this is clear now, and thanks again

MARZIOTULLIO · Jun 29, 2019

	A	B	C	D
1	Code	Date
2	1DXRBCNHCL	2/1/2013		Sheet1
3	1DCTXLTRHD	1/5/2012
4	1DXRBCNHCL	2/1/2012
5	1ETPSD	10/7/2015
6	1DCTXLTRHD	1/5/2012
7	1ETPSD	2/1/2012
8	1ETPSD	2/1/2012
9	1DXRBCNHCL	2/1/2015
10	1DCTXLTRHD	1/9/2015
11	1DXRBCNHCL	10/22/2015

	A	B	C	D	E	F	G
1	Code	2012	2013	2014	2015		Sheet 2
2	1DCTXLTRHD	1	0	0	1
3	1DXRBCNHCL	1	1	0	2
4	1ETPSD	1	0	0	1

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]""

Countifs ignoring duplicate dates in specific range

-=NO=-

New Member

Excel Facts

DanteAmor

Well-known Member

-=NO=-

New Member

DanteAmor

Well-known Member

-=NO=-

New Member

Aryatect

Active Member

DanteAmor

Well-known Member

-=NO=-

New Member

-=NO=-

New Member

MARZIOTULLIO

Well-known Member

Similar threads

Forum statistics

Share this page

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

Countifs ignoring duplicate dates in specific range

New Member

Excel Facts

Well-known Member

New Member

Well-known Member

New Member

Active Member

Well-known Member

New Member

New Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock