adding data across multiple sheets that meet several criteria

Each workday I track the number of patients into our clinic. With each patient we mark if they were referred by someone as well as their assessment score and their home State.

I have each day's data on one sheet labelled with the date: Jun1, Jun2, Jun3, etc.) The data on each sheet is in the same format, I just copy the previous day's sheet and enter the current day's data.

I have these sheets housed between a blank "Start" and blank "End" since there are hundreds of these sheets, it makes it easier for some tabulations.


What I'm needing: I need to get a count of the number of patients who: 1) have received a referral 2) are from State "WI" and 3) have a Score on "#9" as a 1. [Note: I will copy this formula and change to "2" and "3") later.

Sample 'ReferralCount' spreadsheet is similar to actual sheet. On ReferralCount:

Formula would be on Totals sheet, C5.
Referral would be a 'Y' in B:B or '<>' in B:B
State would be "WI" in F:F
Score #9 would be '1' in E:E

You really should do yourself a favor and use a single sheet - just add a column of dates, and all your analyses will be easier, including using data queries and data tools like pivot tables.

Here is a macro that will combine your data from all your daily sheets into one sheet; it can handle sheets as far back as 1 year ago from tomorrow.

Please Login or Register  to view this content.