List of Dates with Duplicates; Count by Day of Week and Multiples

[biomathics23]

Hello and thanks for reading,

I have a list of dates in Column A, and want to analyze them between a start date and an end date (inclusive, encoded in two cells elsewhere, user-entered); within the list, some dates are duplicated by design to represent multiple occurrences of a given event. When the event does not occur, that date is absent from the list. There should be no blanks and the dates are in chronological order.

I want to create a table that indicates how frequently by weekday there are likely to be 0, 1, 2, etc. occurrences of the event on the same day. In other words a table like the following:

[TABLE="width: 550"]
<tbody>[TR]
[TD]Monday[/TD]
[TD]Tuesday[/TD]
[TD]Wednesday[/TD]
[TD]Thursday[/TD]
[TD]Friday[/TD]
[TD]Saturday[/TD]
[TD]Sunday[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0 events[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1 event[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2 events[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]3+ events[/TD]
[/TR]
</tbody>[/TABLE]

So a 3 in the cell below Monday should indicate that within the given time frame, there were three Mondays with no events. A 4 at the bottom of the Thursday column should mean there were 4 Thursdays with at least 3 events during the time frame.

I managed to fill in the first row by taking the # of weekdays between two dates and subtracting the number of unique weekdays in a list to get the number of each weekday with 0 events. But I am at a loss to fill in the remaining rows by appropriate formulae. I strongly prefer not using VBA or helper cells if at all possible.

Thanks for your help!

 

[Rijnsent]

Hi Biomathics,
I found two functions that can help you:
=SUM(N(FREQUENCY(A1:A6,A1:A6)=1)) -> no criteria, but gives the number of unique values that occur once in the set

=SUM(IF(("Tom"=$C$2:$C$20)*($D$2:$D$20<=DATE(2016, 9, 30)*($D$2:$D$20>=DATE(2016, 9, 1))), 1/COUNTIFS($C$2:$C$20, "Tom", $A$2:$A$20, $A$2:$A$20, $D$2:$D$20, "<="&DATE(2016, 9, 30),$D$2:$D$20, ">="&DATE(2016, 9, 1))), 0) -> https://www.extendoffice.com/docume...unique-values-based-on-multiple-criteria.html

The second formula should more or less solve your issue.
Hope that helps,
Koen

 

[biomathics23]

Thanks Rijnsent,

I appreciate that link as I had not seen that formula arrangement before. Although in looking it over, it is still not clear to me that it can/would do what I want. For example, in the formula you just gave, it picked out the three sales Tom made in September, and each was unique. But Tom made a sale at the end of August that was identical to one of his sales in September. If the date period were expanded to include August, I would want a formula that would completely skip both the duplicate sale and the original one that was identical to it, so that it returned the sales that happened exactly once, and then a formula that would that would return sales that happened exactly twice, etc. It is not finding "unique" entries, but about finding "occurs exactly n times" entries; and that also leaves out the question of how to limit the search to only particular days of the week within the start/end dates.

I will try to see what I can do, but if you have any more insights I welcome your feedback.

 

[Rijnsent]

Hi Biomathics,
nice challenge . I created a mockup sheet: a list of dates in column A (starting at A2) and the weekday next to it in column B.

In G3 the "FromDate"
In G4 the "ToDate"

G5 - total occurences in selection: =COUNTIFS($A$2:$A$101,">="&G3,$A$2:$A$101,"<="&G4)
G6 - total unique days in all data: =SUM(--(FREQUENCY($A$2:$A$101,$A$2:$A$101)>0))
G7 - nr of unique days in selection: =G4-G3+1

Next, I copy-pasted your cross table and put in this function for Wednesday (weekday = 3), 2 occurences:
{=SUM(--(FREQUENCY(IF(($A$2:$A$101>=$G$3)*($A$2:$A$101<=$G$4)*($B$2:$B$101=3),MATCH($A$2:$A$101,$A$2:$A$101,0)),ROW($A$2:$A$101)-ROW($A$2)+1)=2))}
This is an array formula, copy-paste without the {} and use CTRL+SHIFT+ENTER to make it work

That function works for all weekdays, items occuring once or more. Days not in the data are harder to find, I found this link might help you with that:
https://www.get-digital-help.com/20...ng-values-in-two-columns-using-excel-formula/

Hope that helps,

Koen

 

[biomathics23]

Thanks Koen, that worked beautifully!

I also used the Weekday and Match/Text/Row/Indirect functions to eliminate the need for your helper column B, and then solved the days not in the data set by finding the total number of Mondays etc. in the given date window and subtracting the sum of the days that had at least one case.

Again, thank you!