Counting How Many Times Names Appear in a Worksheet

Hey all,

Excel newbie here. I'm trying to figure out a way to count how many times a week individual members of my team contact their clients. As social workers, we're required to maintain a certain amount of contact with our clients over a 30-day period. Depending on the severity of individual clients' condition or the duration of their crisis (short-term, long-term, or permanent), they may be seen as often as every day or as seldom as once a month. Generally, it's considered a problem if a client has not been contacted (in person, by phone, or through a "collateral contact") for more than 30 days. Every week, the members of my team are required to submit a list of the clients they saw that week. The list also includes other information about where they saw the client, the service rendered, and the duration of the meeting.

My challenge is this. Since my team members are already submitting their contact logs in Excel format, I would like to create a monthly report that counts the number of times EACH client was seen in any month (or 4-week period). Ideally, I'd be able to use the dates of the contacts (also included in the contact logs) to create a line graph to show a visual representation of such contacts. Even more ideally, it would be great if I could automatically flag the names of people who have not been seen in that month.

However, since my Excel skills are still relatively weak, I'd settle for a simple table or chart that shows a list of each staff member's client list with a number (for the amount of times they were seen in a month) next to the name.

Can anyone help?

Originally Posted by Haseeb A

Hello and welcome to the forum.

It would be helpful to see a sample file. Forum rule says how to attach a file. replace all confidential data with dummy, if there is.

Hello, thank you so much for the reply. I have attached a sample file, with all client names changed. As you can see, we may see the same clients more than once in any week, and sometimes more than once on the same day. I'd like to be able to figure out a way to count how many times each client name appears in the log (each log covers a one-week period). Ultimately, if I could learn how to do it, I'd like to create a monthly visual representation of each staff member's client log so we can see how often a client is seen in any given 30-day period. Of course, I'd have to merge multiple logs but I know how to do that.

The real challenge is to learn how to use the count feature. For each person on my staff, I have a "master list" of clients, which would serve as the primary "base" document. Each week, I would merge the contact logs into this master list. At the end of 30 days (or a 4-week period), I would be able to see which clients were seen and which were not. Every client would have a count of 1 (due to inclusion in the "master list)." This means that any client with a count value of 1 would actually not have been seen that month. Every other client who was seen would have a count of x+1, with x being the actual number of times they were seen and 1 being the count value for simply being on the list.

Any help you can offer would be tremendously appreciated.

Thanks so much!