Using SUMPRODUCT to display Trends on a Weekday basis

[anaschiliro]

Good afternoon,

I have a long list of data, about 16 thousand rows.
This list has information regarding attendance of a group.
I’d like to be able to show a pattern of absence on a weekly basis.
The bottom line: I’d like to see how many times Mary has called out this month and if those calls happen to be mostly on Thursdays.

Consecutive dates count as one occurrence.

For example:

One Occurrence:
01/01/2017
01/02/2017
01/03/2017


Single Occurrences:
01/01/2017
01/03/2017
01/05/2017


I've used SUMPRODUCT on my final report to display how many times Mary has called out. It works great!

Now I have a different sheet, that looks like a calendar, that Id like to use to display when those sick calls happen (by weekday), without interfering with my final results.
I was able to come up with a SUMPRODUCT formula, but it only works for single occurrences, meaning, it doesn’t work properly with consecutive dates counted as one occurrence. I either get a zero in one of the days or it affects my final results (information won’t match).

I’m attaching a file as an example.

Thank you for time reading and responding this.

[JeteMc]

Welcome to Excel Forum.
If I understand correctly then the following formula will place the 'ones' in the cells:

Please Login or Register  to view this content.

The cells are filled using three conditional formatting 'equal to' (1) rules.
Let us know if you have any questions.

[anaschiliro]

Thank you!
I ran a test and having the "1" in place does the trick.
I appreciate your help very much.

[JeteMc]

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools link above your first post. I hope that you have a blessed day.