counting contact events in contact log by case within varying date ranges

[Trees]

I have a contact log file with case#, Log# and Log# among other items, I have a customer file with Case#. For each Case# I want to be able to get a count of contact events, where the count is more than 1, that happend on that logdate, in the range of that logdate and the prior 4 days (5 day count), a 10day count and an over 10 day count.

So i can report how many cases were associated with each count, knowing that there is double counting.

I want to know who is having to make multiple contacts within these time frames. subsequently I would want to know how many contacts thre were for that case# within each timeframe.

Not sure this can be solved with Excel or Excel/VBA but hoping someone out there knows how.

No file uploaded as I have nothing really to show but raw data which is restricted by my company. I have no initial solution as yet.

 

[Luke M]

Hi Trees,

Could you create a sample workbook showing desired layout (column headers, and mock formula results). Does your raw data have date(s) associated with each row? I understand it's proprietary, but a fake example would help. If you want help generating random data:
http://www.generatedata.com/

 

[Trees]

Uploaded file with example data and desired outcome with known solution for 2 of the counts using COUNTIF, it is the other 2 I have no solution for. for a given record how do i count how many records there are within a date range relative to the contact date of the current row/record.

 

[Trees]

Thank you Luke for the initial response, you are right that this file will clarify the objective

 

[Luke M]

Perfect, thanks Trees. Looks like we need to write some COUNTIFS functions, actually. Take a look at the attached. Not sure if I got your date ranges correct, but should get you the syntax.

 

[Trees]

Awesome!...as promoted..I just looked at it and it looks exactly like what I was wanting. Thank you Luke!