Counting date occurrenceis for a list of non contiguous dates

[taher9990]

Dears kindly help me with this

I have this table The expected result is in column No. of Days
So it must count number of days for these Products (1010,1030,1040,1020)

I have this formula which give similar results but for dates that have gap like 21\01 then 24\01 here it will jump from 14 to 11 so the number of days will be 18,17,16,15,14,11

=INDIRECT(CONCATENATE("$B";"$";MIN(ROW([Date]))+ROWS([Date])-1))-B1

ID	Date	No. Days
1010	1/17/2016	18
1030	1/17/2016	18
1040	1/17/2016	18
1020	1/17/2016	18
1010	1/18/2016	17
1040	1/18/2016	17
1030	1/18/2016	17
1020	1/18/2016	17
1020	1/19/2016	16
1010	1/19/2016	16
1040	1/19/2016	16
1030	1/19/2016	16
1020	1/20/2016	15
1010	1/20/2016	15
1040	1/20/2016	15
1030	1/20/2016	15
1020	1/21/2016	14
1040	1/21/2016	14
1010	1/21/2016	14
1030	1/21/2016	14
1020	1/24/2016	13
1040	1/24/2016	13
1010	1/24/2016	13
1030	1/24/2016	13
1040	1/25/2016	12
1030	1/25/2016	12
1010	1/25/2016	12
1020	1/25/2016	12
1010	1/26/2016	11
1020	1/26/2016	11
1030	1/26/2016	11
1040	1/26/2016	11
1020	1/27/2016	10
1030	1/27/2016	10
1040	1/27/2016	10
1010	1/27/2016	10
1010	1/28/2016	9
1020	1/28/2016	9
1030	1/28/2016	9
1040	1/28/2016	9
1010	1/31/2016	8
1020	1/31/2016	8
1030	1/31/2016	8
1040	1/31/2016	8
1010	2/1/2016	7
1020	2/1/2016	7
1030	2/1/2016	7
1040	2/1/2016	7
1010	2/2/2016	6
1020	2/2/2016	6
1030	2/2/2016	6
1040	2/2/2016	6
1010	2/3/2016	5
1020	2/3/2016	5
1030	2/3/2016	5
1040	2/3/2016	5
1010	2/4/2016	4
1020	2/4/2016	4
1030	2/4/2016	4
1040	2/4/2016	4
1010	2/7/2016	3
1020	2/7/2016	3
1030	2/7/2016	3
1040	2/7/2016	3
1010	2/8/2016	2
1020	2/8/2016	2
1030	2/8/2016	2
1040	2/8/2016	2
1010	2/9/2016	1
1020	2/9/2016	1
1030	2/9/2016	1

<colgroup><col><col><col></colgroup><tbody>
</tbody>

 

[oldbrewer]

there are clearly 18 rows with 1010 all with different dates. Say if 2 dates were the same, would you want answer = 17 ?

 

[oldbrewer]

ID	Date
1010	03/01/2016
1030	04/01/2016
1010	05/01/2016
1020	06/01/2016				Count of Date	Date
1010	07/01/2016				ID	03/01/2016	04/01/2016	05/01/2016	06/01/2016	07/01/2016	08/01/2016	09/01/2016	Grand Total
1040	08/01/2016				1010	3	2	2		3	1	2	13
1030	09/01/2016				1020	1	2	3	1	1	2	1	11
1020	03/01/2016				1030		2	1	1	1	1	1	7
1020	04/01/2016				1040	2			4	1	1	1	9
1010	05/01/2016				Grand Total	6	6	6	6	6	5	5	40
1040	06/01/2016
1010	07/01/2016
1020	08/01/2016
1010	09/01/2016
1040	03/01/2016
1030	04/01/2016
1020	05/01/2016
1040	06/01/2016
1010	07/01/2016				a pivot table tells you how many 4 digit codes in the first column
1030	08/01/2016
1010	09/01/2016				how many different dates in second column
1040	03/01/2016
1010	04/01/2016				how many times a date is duplicated for a code
1020	05/01/2016				(1010 has three 3/1/16, two 4/1/16, two 5/1/16 and no occurrences of 6/1/16)
1040	06/01/2016
1030	07/01/2016
1010	08/01/2016				also how many times a particular date appears
1020	09/01/2016
1010	03/01/2016
1020	04/01/2016
1030	05/01/2016
1040	06/01/2016
1020	07/01/2016
1020	08/01/2016
1040	09/01/2016
1010	03/01/2016
1010	04/01/2016
1020	05/01/2016
1030	06/01/2016
1040	07/01/2016

<colgroup><col><col><col span="3"><col><col span="7"><col><col></colgroup><tbody>
</tbody>

 

[taher9990]

Thanks a lot dude,
But that's not what I want, what I want is only to count how many times is that product appeared in the list and that counter must be decremented until the recent day in the list , the pivot table is giving a lot of info despite of it is useful an more elegant but it is not related to what I want,
What I want is exactly as I have shown in the first thread, I need the counter because I use it in another calculations e.g. If product hsa

ID DATE Counter
1010 14/05/2016 3
1020 14/05/2016 3
1030 14/05/2016 3
1010 15/05/2016 2
1020 15/05/2016 2
1030 15/05/2016 2
1010 16/05/2016 1
1020 16/05/2016 1
1030 16/05/2016 1

As you can see the recent date is counted as 1 and oldest date is counted as 3

Below formula is doing getting almost close the same result but if there is a gap in date e.g. 14/05 and then 16/05 the counter will be 1,2,5 it will not continue in the sequence

=INDIRECT(CONCATENATE("$B","$",MIN(ROW([Date]))+ROWS([Date])-1))-B1

A B C
ID DATE Counter
1010 13/05/2016 5
1020 13/05/2016 5
1030 13/05/2016 5
1010 16/05/2016 2
1020 16/05/2016 2
1030 16/05/2016 2
1010 17/05/2016 1
1020 17/05/2016 1
1030 17/05/2016 1

 

[amxtomzo]

Hello
make a back up file to test this

I don't know if I got this
this formula assumes that column A, starting in row 1, is populated with 1010, 1020, 1030, 1040
This formula assumes column B, starting in row 1, are your date

that said......
In cell C1 put a 1
In Cell C2, put this formula, Then Copy this formula as far down the work sheet as you need

=IF(A2=A1,C1+1,COUNTIF($A$2:A3, A2))

Thomas

 

[Aladin Akyurek]

In C2 control+shift+enter, not just enter, and copy down:

=SUM(IF($A$2:$A$72 = $A2,IF($B2 < $B$2:$B$72,1)))+1

 

[taher9990]

In C2 control+shift+enter, not just enter, and copy down:

=SUM(IF($A$2:$A$72 = $A2,IF($B2 < $B$2:$B$72,1)))+1

It is giving the expected results as you can see in row 45 it reached to 1 then it starts counting again
thanks a lot

ID	Date	No. Days
1010	1/17/2016		11
1030	1/17/2016		11
1040	1/17/2016		11
1020	1/17/2016		11
1010	1/18/2016		10
1040	1/18/2016		10
1030	1/18/2016		10
1020	1/18/2016		10
1020	1/19/2016		9
1010	1/19/2016		9
1040	1/19/2016		9
1030	1/19/2016		9
1020	1/20/2016		8
1010	1/20/2016		8
1040	1/20/2016		8
1030	1/20/2016		8
1020	1/21/2016		7
1040	1/21/2016		7
1010	1/21/2016		7
1030	1/21/2016		7
1020	1/24/2016		6
1040	1/24/2016		6
1010	1/24/2016		6
1030	1/24/2016		6
1040	1/25/2016		5
1030	1/25/2016		5
1010	1/25/2016		5
1020	1/25/2016		5
1010	1/26/2016		4
1020	1/26/2016		4
1030	1/26/2016		4
1040	1/26/2016		4
1020	1/27/2016		3
1030	1/27/2016		3
1040	1/27/2016		3
1010	1/27/2016		3
1010	1/28/2016		2
1020	1/28/2016		2
1030	1/28/2016		2
1040	1/28/2016		2
1010	1/31/2016		1
1020	1/31/2016		1
1030	1/31/2016		1
1040	1/31/2016		1
1010	02/01/2016		18
1020	02/01/2016		18
1030	02/01/2016		18
1040	02/01/2016		17
1010	02/02/2016		17
1020	02/02/2016		17
1030	02/02/2016		17
1040	02/02/2016		16
1010	02/03/2016		16
1020	02/03/2016		16
1030	02/03/2016		16
1040	02/03/2016		15
1010	02/04/2016		15
1020	02/04/2016		15
1030	02/04/2016		15
1040	02/04/2016		14
1010	02/07/2016		14
1020	02/07/2016		14
1030	02/07/2016		14
1040	02/07/2016		13
1010	02/08/2016		13
1020	02/08/2016		13
1030	02/08/2016		13
1040	02/08/2016		12
1010	02/09/2016		12
1020	02/09/2016		12
1030	02/09/2016		12

<colgroup><col><col span="2"><col></colgroup><tbody>
</tbody>

 

[Aladin Akyurek]

It is giving the expected results as you can see in row 45 it reached to 1 then it starts counting again
thanks a lot
[...]

Is it ok or what?

 

[taher9990]

Sorry for the mistype, It is giving unexpected results, it not OK dude sorry for that.

 

[Aladin Akyurek]

Sorry for the mistype, It is giving unexpected results, it not OK dude sorry for that.

In C2 control+shift+enter and copy down:

=IF(ISNUMBER(MATCH(B2,$B$1:B1,0)),VLOOKUP(B2,$B$1:C1,2,0),SUM(IF($A$2:$A$72=$A2,IF($B2<$B$2:$B$72,1)))+1)

Does this satisfy? If not, would you care to elaborate?