need a formula or a vba code to count rows based on criteria

[Ingeoa]

Hi,
I need to count the number of times a test is analyzed where one should only count new cases at 1 month intervals for each patient.
See attached image.

The X’s are only there to illustrate which rows I want to count.

I have tried using an if-formula but to no success.

Hope there is someone who can help with this

 

[mart37]

Why do you not count 20.02.2020?

 

[mart37]

Map4
	A	B	C
1	patient id	sampling date	count
2	1	25-1-2020	9
3	1	20-2-2020
4	1	15-3-2020
5	2	16-7-2020
6	2	17-7-2020
7	3	18-7-2020
8	3	20-8-2020
9	4	1-1-2020
10	4	6-1-2020
11	4	10-2-2020
12	4	20-3-2020
Blad1

Cell Formulas
Range	Formula
C2	C2	=SUMPRODUCT(IF(FREQUENCY(MATCH((A2:A12&"|"&YEAR(B2:B12)&"-"&MONTH(B2:B12)),(A2:A12&"|"&YEAR(B2:B12)&"-"&MONTH(B2:B12)),0),MATCH((A2:A12&"|"&YEAR(B2:B12)&"-"&MONTH(B2:B12)),(A2:A12&"|"&YEAR(B2:B12)&"-"&MONTH(B2:B12)),0))>0,1))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Ingeoa]

Why do you not count 20.02.2020?

Thanks for the help. Seems to be on to something here.
The difference between 25.01.2020 and 20.02.2020 is less than 1 month and should not be counted.

 

[mart37]

What about 25.02.2020?
A month is the total of days in that month? So january is 31 and february is 28 or 29.

 

[Ingeoa]

What about 25.02.2020?
A month is the total of days in that month? So january is 31 and february is 28 or 29.

I see your point. I have not been clear enough here. I want the formula to be based on 31 days. Sorry about that.

 

[mart37]

Something like this:

unieke waarden tellen per maand.xlsx
	A	B	C	D
1	patient id	sampling date	difference	unique
2	1	25-1-2020	0	1
3	1	20-2-2020	26	0
4	1	15-3-2020	24	0
5	2	16-7-2020	0	1
6	2	17-7-2020	1	0
7	3	18-7-2020	0	1
8	3	20-8-2020	33	1
9	4	1-1-2020	0	1
10	4	6-1-2020	5	0
11	4	10-2-2020	35	1
12	4	20-3-2020	39	1
13
14			count	7
Blad1

Cell Formulas
Range	Formula
C3:C12	C3	=IF(A3=A2,B3-B2,0)
D3:D12	D3	=IF(OR(C3=0,AND(A3=A2,C3>31)),1,0)
D14	D14	=SUM(D2:D13)

 

[Ingeoa]

Something like this:

unieke waarden tellen per maand.xlsx
	A	B	C	D
1	patient id	sampling date	difference	unique
2	1	25-1-2020	0	1
3	1	20-2-2020	26	0
4	1	15-3-2020	24	0
5	2	16-7-2020	0	1
6	2	17-7-2020	1	0
7	3	18-7-2020	0	1
8	3	20-8-2020	33	1
9	4	1-1-2020	0	1
10	4	6-1-2020	5	0
11	4	10-2-2020	35	1
12	4	20-3-2020	39	1
13
14			count	7
Blad1

Cell Formulas
Range	Formula
C3:C12	C3	=IF(A3=A2,B3-B2,0)
D3:D12	D3	=IF(OR(C3=0,AND(A3=A2,C3>31)),1,0)
D14	D14	=SUM(D2:D13)

Something like this:

unieke waarden tellen per maand.xlsx
	A	B	C	D
1	patient id	sampling date	difference	unique
2	1	25-1-2020	0	1
3	1	20-2-2020	26	0
4	1	15-3-2020	24	0
5	2	16-7-2020	0	1
6	2	17-7-2020	1	0
7	3	18-7-2020	0	1
8	3	20-8-2020	33	1
9	4	1-1-2020	0	1
10	4	6-1-2020	5	0
11	4	10-2-2020	35	1
12	4	20-3-2020	39	1
13
14			count	7
Blad1

Cell Formulas
Range	Formula
C3:C12	C3	=IF(A3=A2,B3-B2,0)
D3:D12	D3	=IF(OR(C3=0,AND(A3=A2,C3>31)),1,0)
D14	D14	=SUM(D2:D13)

Row 4 should be counted. Patient 1 has first one test. The second test is within 31 day form the last test and is not counted. Test 3 is over 31 day from last counted test. Therefore it should be counted.