Hi there -

Our clinic has been keeping tracking of the amount of patients we are enrolling in Affordable Care Act insurance. I am trying to sum the data to display the weekly enrollment totals for our two enrollment counselors. The tricky part is that my data sheet has four columns that list the initial appointment date, the follow up appointment, 2nd follow up appointment, and 3rd follow up appointment. Not all patients come in for this many follow up appointments, but some do.

I want a formula to "scan" all columns that contain a date (starting by scanning 3rd follow up appointment and finishing by scanning the initial appointment date). If the 3rd follow up appointment date doesn't fall into the date range of the week I want to sum, then I want the formula to move onto the next column (2nd follow up appointment). I then want the sum range (the column containing number of successful applicants) to be summed once the formula finds a date that satisfies that week's date range, and then stop scanning the following cell dates.

I've attached a screenshot of a portion of the data so you can see how it's formatted:
Untitled.jpg

I've been playing around with the SUMIF function, but if more than one column (all appointment types) satisfies the same week's date conditions, then I don't want it to sum "successful applicants" twice. How do I stop the redundancy??

Sorry if this is totally confusing. I may just need to end up formatting my spreadsheet differently or making a pivot table. I'd appreciate any available thoughts. So far it's given me quite the headache!

Cheers,
Amanda