Calculate Total Number of Dates within a Month per Department

Master Sheet:

Dept            Date

Dept A         12/2/18

Dept B         12/4/18

Dept C         12/4/18

Dept B         12/5/18

Dept B         12/6/18

.

.

Summary Sheet:

Dept           Jan Count    Feb Count    Mar Count     ...    Dec Count

                         1                    2                  3                ...           12        (row 1)

Dept A         Formula        Formula        Formula          ...     Formula

Dept B         Formula        Formula        Formula         ...      Formula

Dept C         Formula        Formula        Formula         ...     Formula

.

.

Formula:

=COUNTIFS({Master Sheet Range 1}, @cell = Dept@row, {Master Sheet Range 2}, MONTH(@cell) = [Jan Count]$1)

.

.

{Master Sheet Range 1}: the Dept column on the master sheet

{Master Sheet Range 2}: the Date column on the master sheet

.

.

In the first row of your summary sheet, enter the month number in each corresponding month count column. It is important to include the $ in the formula above as it will lock that row reference in as you drag fill.

Row 2 of the summary sheet is where you will start your dept listing.

You can then enter the formula above (change column names as needed) into the second column of row two on your summary sheet ([Jan Count]2).

From there you can drag fill on over and down to auto-populate the rest of the cells.

The formula is set up to look at the department name for whatever row it is in, and will look at row one of whatever column it is in to determine the month to count for that department.