How to sum cells based off of a date range

Hello!

I have two concerns that need solutions based off of the same problem. I attached a sample workbook. On my data sheet I need to count specific information from the worksheets labeled from January to December 2013. For instance, lets say I need the total completed discharges for March 2013. I thought it was as simple as counting all of the cells with "complete" in them under the TOD column on each respective sheet (countif). Unfortunetly the "completes" for March are based off of the DOD column (date of discharge), which means just because it says "complete" in that month, does not mean the client actually completed treatment in that month. YIKES! So, i need to be able to count specific criteria for each month based off of a date range that spreads across 12 sheets of data. Is that possible? My next concern is how to figure out the LOS (length of Stay) for each TOD (type of discharge) for each month. That is calculated by the difference between the DOA column (date of arrival) and the DOD column. Once again, i thought it was an easy solution, but ran into the same problem above. Any help would be greatly appreciated! Thanks in advance!

Hi hemesh,

the type of discharge is determined by how a client leaves our facility, and is not relavent to what I am looking for. However, the client recieves a type of discharge (complete, AMA, medical, etc) when they leave our facility. The date that they leave is how I need to count each type of discharge for each month. It's called the DOD (date of discharge) if the DOD is 5/25/13 and the TOD is Complete, then it should be counted in the May slot under Complete. does that make sense?

FDibbins,

I would love to, unfortunetely this is sample data from a very large treatment census for a hospital, and that is how they decided to keep track of their admissions. They want to track other information from this data, and I am the one tasked with this monster! Thanks for your help anyway!

So,

If i could somehow get all of the information onto one sheet, I would then be able to match the DOD with the TOD and collect my data for each month that way? How would the formula look then? :-)

Unless the array formula that hemesh so graciously provided can be modified to work across multiple sheeets?

hemesh,

thanks for your quick responses. However, I tried something like that already. It works great if it was just a simple match and count issue, but the LOS for "complete" for January is based off of the DOD (date of discharge). Many clients may be admitted in January, but discharged in a different month. Which makes the problem much more challenging, becuase now I need to search for "complete" in a date range through multiple sheets, and somehow sum them on my data sheet under the respective cell. Better clarification?

FDibbins,

Thank you for the neat trick! It may be very helpful in the future. Unfortuntely for me, at the moment it doesn't quite do the trick. For instance, in the February sheet, some of the "complete" may have a DOD (date of discharge) of a different month, and therefore need to be counted in their respective month, not February. Blah

hemesh,

That works great for January! Unfortunetly, the DOD's that happen in other months on the January Sheet will now be unaccounted for going forward. I don't suppose there is a way to use that array formula over multiple sheets? Maybe this is a lost cause? lol

hemesh,

THanks, I think I will actually give that a shot! I appreciate your help! It looks like it will work just fine under those conditions! Thank you again!

Last question,

With the data dump sheet in place, your formula works for January, but how do I get it to work for the other months? Thanks!

FDibbins,

That could be the solution to working with multiple sheets. i could just hide all of the excess data. One thing I noticed is that the formula is counting feb 1st as part of January, but otherwise it looks great!

Actually, by taking out the second = sign, it remedies that issue! Thanks!

hemesh,

That's works wonderfully! I really appreciate your help. I really appreciate everyone's help! I have learned a lot in the process. Thanks again everyone!

Can you prepare it manually for 2 months with less data and upload the file