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: 15

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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)
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,048
Members
448,543
Latest member
MartinLarkin

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
Back
Top