Summing with date difference and conditions

Hello everyone, here is what I have to do. In my data file I have a client's date of birth and other several date columns: Client DOB, Client Intake, Client Start and Client Referral Date. I want to be able to write a formula that will sum the date difference between each of the date columns and Client DOB by condition and put that information into tables. For example, I want to sum groups of "date diff" between client DOB and Client Intake by age categories by month:
16-19 months
20-25 months
26-30 months
and so on.

I am hoping I can do each with one formula - realizing it will be a long formula for each of the Date columns and Client DOB.

Thank you for your help.

Zeda

Any formula we give you will necessarily have to refer to the specific columns where those dates are located, so can you tell us which columns they are, or, better still, attach a sample Excel workbook so we can see for ourselves (and try out different approaches with the data).

To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

Note that the Paperclip icon does not work.

Hope this helps.

Pete

Pete_UK,

Thank you for the help. I'm attaching an extract -this file has about 11,000 cases and 34 columns. of data. But here are the relevant columns with some test data.

Zeda

Thanks for submitting that, Zeda, but I'm just about to go out now for the evening - I'll check back later if no-one else has chipped in.

Pete

No problem. Have a good time.

Zeda.

Hi Zeda,

I have set up a summary table in columns H to L of your file, with regular month spacing (of 5 months) in columns H and I - you can adjust the months in column I if you want the spacing to be different for each group, and column H will automatically adjust.

Then I have used this formula in cell J2:

=SUMPRODUCT((DATEDIF($B$2:$B$16,C$2:C$16,"m")>=$H2)*(DATEDIF($B$2:$B$16,C$2:C$16,"m")<$I2))

This can be copied across and down as required to complete the table.

You will need to adjust the ranges in the formula (the $16 part) to suit the amount of data that you have in your real file, as well as adjusting the column references to suit where your dates actually occur.

Hope this helps.

Pete

Thank you Pete_UK! That makes a lot of sense. I appreciate your help. A great solution. It works perfectly for what i need.

Awesome.

Zeda

Glad to be able to help, and thanks for marking the thread as Solved.

You might also like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete