Calculate Total Number of Dates within a Month per Department

EmilyEdens ✭✭
edited 12/09/19 in Formulas and Functions


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!!!




  • Paul Newcome
    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




    =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.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!