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

virtuosok

Board Regular
Joined
Sep 2, 2020
Messages
209
Office Version
  1. 365
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

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
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!
 
Upvote 0
Solution
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)))
 
Upvote 0
Oh, I see, I misunderstood. Glad you were able to amend the formula as desired.

Cheers!
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,947
Members
448,534
Latest member
benefuexx

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