• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

Trees

New Member
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.
 
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/
 
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.
 

Attachments

  • EventCounts.xlsx
    14.2 KB · Views: 3
Thank you Luke for the initial response, you are right that this file will clarify the objective
 
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.
 

Attachments

  • EventCounts LM.xlsx
    14.5 KB · Views: 2
Awesome!...as promoted..I just looked at it and it looks exactly like what I was wanting. Thank you Luke!
 
Back
Top