Help setting up some formulas for a spreadsheet of daily data reports...

Good Evening!

I have a project where the main goal is to summarize / analyze certain characteristics of medication use within in a healthcare facility.

For data, I have been given a number of individual daily medication use reports in Excel file format (e.g., 30 separate excel files for each day of a given month).

So far, I have batch-combined these into one spreadsheet...I have uploaded a simplified mock version to represent what the dataset currently looks like (The "real" report has 15 columns of data).


Now, I would like to determine the following from this database (in the most efficient way possible!):

1-Determine the # of days of CONSECUTIVE use of the medication for each individual patient in the database (the “duration of treatment”). *Note that there will be many, many patients in the real database (100+)...each with a unique numerical identifier

2-Determine the total number of patients who received the medication based on certain qualifiers (e.g., "in a certain location of the facility", "during a certain given week, month, etc."). *Note that there will be no more than 8 unique location options)

3-Provide summary characteristics for the medication use in the facility, such as:
--mean/median duration of treatment, etc.
--the # of patients who received more than “x” consecutive days of therapy
--the # of patients who received a certain dose (note that there will be a limited number (<5) of possible doses)


I know from doing some quick searches of the forum so far that these types of things CAN be done fairly efficiently on a dataset like what I’m getting through use of some of the more complicated Excel formula functions...BUT many of these I’m not yet 100% comfortable developing/using yet...thus my post!

So, I’d welcome any and all advice on how to sort my data, transform it [if needed], set up some formulas, etc to do my task without wasting huge amounts of time...

Thanks in advance for all help!

this tackles every request stated in there, one could change the dates (interval)