Calculate Total Number of Dates within a Month per Department

EmilyEdens

Hello,

I am a total newbie with SmartSheet and on the struggle bus with this calculation quest. Here is what I am trying to do:

I have a sheet entitled 'ES Monitoring' that each department needs to fill in at least daily to track safety behaviors within their department. The columns in this sheet that I want to summarize in a separate sheet are the entry date and department for which I want the total number of entries that each department made for each month of the year.  

Thank you in advance!!!

 

Emily

Paul Newcome

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.