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

#### Ingeoa

##### New Member
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

#### Attachments

• 1E0BEC3E-D006-4974-8430-65AC65EF9926.jpeg
113.3 KB · Views: 13

### Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

#### mart37

##### Well-known Member
Why do you not count 20.02.2020?

#### mart37

##### Well-known Member
Map4
ABC
1patient idsampling datecount
2125-1-20209
3120-2-2020
4115-3-2020
5216-7-2020
6217-7-2020
7318-7-2020
8320-8-2020
941-1-2020
1046-1-2020
11410-2-2020
12420-3-2020
Cell Formulas
RangeFormula
C2C2=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

##### New Member
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

##### Well-known Member

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

#### Ingeoa

##### New Member
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

##### Well-known Member
Something like this:
unieke waarden tellen per maand.xlsx
ABCD
1patient idsampling datedifferenceunique
2125-1-202001
3120-2-2020260
4115-3-2020240
5216-7-202001
6217-7-202010
7318-7-202001
8320-8-2020331
941-1-202001
1046-1-202050
11410-2-2020351
12420-3-2020391
13
14count7
Cell Formulas
RangeFormula
C3:C12C3=IF(A3=A2,B3-B2,0)
D3:D12D3=IF(OR(C3=0,AND(A3=A2,C3>31)),1,0)
D14D14=SUM(D2:D13)

#### Ingeoa

##### New Member
Something like this:
unieke waarden tellen per maand.xlsx
ABCD
1patient idsampling datedifferenceunique
2125-1-202001
3120-2-2020260
4115-3-2020240
5216-7-202001
6217-7-202010
7318-7-202001
8320-8-2020331
941-1-202001
1046-1-202050
11410-2-2020351
12420-3-2020391
13
14count7
Cell Formulas
RangeFormula
C3:C12C3=IF(A3=A2,B3-B2,0)
D3:D12D3=IF(OR(C3=0,AND(A3=A2,C3>31)),1,0)
D14D14=SUM(D2:D13)
Something like this:
unieke waarden tellen per maand.xlsx
ABCD
1patient idsampling datedifferenceunique
2125-1-202001
3120-2-2020260
4115-3-2020240
5216-7-202001
6217-7-202010
7318-7-202001
8320-8-2020331
941-1-202001
1046-1-202050
11410-2-2020351
12420-3-2020391
13
14count7
Cell Formulas
RangeFormula
C3:C12C3=IF(A3=A2,B3-B2,0)
D3:D12D3=IF(OR(C3=0,AND(A3=A2,C3>31)),1,0)
D14D14=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.

Replies
8
Views
95
Replies
1
Views
78
Replies
19
Views
216
Replies
2
Views
410
Replies
13
Views
226

1,129,428
Messages
5,636,197
Members
416,907
Latest member
DTG

### 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.

### Which adblocker are you using?

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

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