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

#### biomathics23

##### New Member

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:

 Monday Tuesday Wednesday Thursday Friday Saturday Sunday 0 events 1 event 2 events 3+ events

<tbody>
</tbody>

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.

### Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

#### Rijnsent

##### Well-known Member
Hi Biomathics,
=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

##### New Member
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

##### Well-known Member
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

##### New Member
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!

Replies
18
Views
476
Replies
7
Views
61
Replies
1
Views
70
Replies
0
Views
140
Replies
0
Views
124

1,136,321
Messages
5,675,063
Members
419,548
Latest member
wfarzand

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back