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

#### virtuosok

##### Board Regular
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

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

#### Domenic

##### MrExcel MVP
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!

#### virtuosok

##### Board Regular
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
Oh, I see, I misunderstood. Glad you were able to amend the formula as desired.

Cheers!

Replies
0
Views
58
Replies
0
Views
89
Replies
5
Views
110
Replies
10
Views
192
Replies
7
Views
311

1,186,112
Messages
5,955,912
Members
438,225
Latest member
rsur

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