# of unique days with any entries in a specified time range for a study subject

virtuosok

Board Regular
Joined
Sep 2, 2020
Messages
141
Office Version
  1. 2016
Platform
  1. Windows
Hi,
I have the following formula which works well for a subset of subjects joining the study at various times throughout the year. It calculates the # of unique days when they logged something in their seizure diary. Sometimes this can mean no entry on a given day whereas sometimes this can be multiple entries if they had a particularly bad day. I'm only looking for the # of days with any entries whatsoever, regardless if this were 1 entry or 100.
{=SUM(SIGN(FREQUENCY(IF('Seizure diary'!$E$13:$E$50000='Post-processed data'!C4,'Seizure diary'!$F$13:$F$50000),'Seizure diary'!$F$13:$F$50000)))}
where
'Post-processed data'!C4 = unique subject identifier
'Seizure diary'!$F$13:$F$50000 = column with dates (only); columns G onwards would have subsequent info regarding seizures, which is of no interest here.


However, can I take it to the next level so that the formula only takes into account the entries in a particular time range, with a criterion?
More specifically, I'm interested in a range between 15 and 5 days from today (the day the report is run). I guess I need to set up helper columns first, to define this range for each subject...
Finally, given that some of the subjects might not even have that data (if they joined recently, i.e. less than 15 days ago), can I add a condition which would trigger the calc in case the subject stays in the study for more than 15 days (otherwise returning N/A)? For the formula above, the date the subject joined the study is in cell 'Post-processed data'!C4
Wondering if I made myself clear...
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,630
To return a unique count based on your criteria, which includes the time range, try...

=SUM(SIGN(FREQUENCY(IF('Seizure diary'!$E$13:$E$50000='Post-processed data'!C4,IF('Seizure diary'!$F$13:$F$50000>=TODAY()+5,IF('Seizure diary'!$F$13:$F$50000<=TODAY()+15,'Seizure diary'!$F$13:$F$50000))),'Seizure diary'!$F$13:$F$50000)))

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!
 
Solution

virtuosok

Board Regular
Joined
Sep 2, 2020
Messages
141
Office Version
  1. 2016
Platform
  1. Windows
Thanks, this works! With a minor tweak as follows
=SUM(SIGN(FREQUENCY(IF('Seizure diary'!$E$13:$E$50000='Post-processed data'!C4,IF('Seizure diary'!$F$13:$F$50000>=TODAY()-15,IF('Seizure diary'!$F$13:$F$50000<=TODAY()-5,'Seizure diary'!$F$13:$F$50000))),'Seizure diary'!$F$13:$F$50000)))
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,630
Oh, I see, I misunderstood. Glad you were able to amend the formula as desired.

Cheers!
 

Watch MrExcel Video

Forum statistics

Threads
1,133,244
Messages
5,657,583
Members
418,401
Latest member
B_A_M155

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