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

Ingeoa

New Member
Joined
Jan 28, 2021
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
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
    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
Joined
Aug 4, 2017
Messages
1,090
Office Version
  1. 2016
Platform
  1. Windows
Why do you not count 20.02.2020?
 

mart37

Well-known Member
Joined
Aug 4, 2017
Messages
1,090
Office Version
  1. 2016
Platform
  1. Windows
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
Blad1
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
Joined
Jan 28, 2021
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
Aug 4, 2017
Messages
1,090
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
Jan 28, 2021
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Aug 4, 2017
Messages
1,090
Office Version
  1. 2016
Platform
  1. Windows
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
Blad1
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
Joined
Jan 28, 2021
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
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
Blad1
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
Blad1
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.
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top