COUNTIF by MONTH in Sheet Summary Field

m_dorseym_dorsey
edited 12/09/19 in Formulas and Functions
11/07/19 Edited 12/09/19

Hello,

I'm trying to count the number of entries created in a month, based on the timestamp from the system generated Created field. I am able to do this with a helper column, where I perform the Month() function from the Created field in a column and then use the Countif formula in the Sheet Summary field (ex: To determine how many were created in June, I use this formula: =COUNTIF(Month:Month, (@cell) = 6)).

However, to avoid having calc columns my team would see when entering items, or mistakenly think they need to provide data for, I'd like to do this all directly in the Sheet Summary field.

I am using the following formula: =COUNTIF(Created:Created, MONTH(@cell) = 6)

and receiving the #INVALID DATA TYPE error. Any thoughts/recommendations on how to resolve this?

Comments

  • L_123L_123 ✭✭✭✭✭

    =COUNTIF(Created:Created, value(MONTH(@cell)) = 6)

     

    that said if you are worried about your team screwing with formulas or data always remember you can hide and/or lock columns.  I often also color code the columns that are available for my team to edit, which gives a quick and easy visual reference for them.

  • AaronMAaronM ✭✭✭✭✭
    edited 06/12/20

    Thank you m_dorsey. I am having the exact issue with a countif. I have followed your example and I am still getting the "#invalid data type" error in the sheet summary pane.

    =COUNTIF([Finish Date]:[Finish Date], VALUE(MONTH(@cell)) = 6)

    Any suggestions?

    I have made sure the finish date column is set to date format


    Edit & Update...

    So the issue is that within that range I had several blank cells. Could you help me add in and isblank or something similar that will allow the formula to ignore the blank cells keeping it from returning #invalid data type?

  • StefanStefan ✭✭✭

    Hello Aaron and m_dorsey,

    I think this may actually be a bug.

    If you use this in an empty cell of your sheet it should work ok:

    =COUNTIF(created:created, IFERROR(MONTH(@cell), 0) = 6)

    IFERROR eliminates the problem with the empty or non date cells in your range.

    If you copy this formula to a field in the sheet summary, it shows "UNPARSEABLE".


    So I suggest to contact Smartsheet about the bug and until solved use a helper column which can be locked and hidden for now.

    Hope this helps

    Stefan

    PS: I take as granted, that your data collection process for entries to your sheet is made to avoid manual (error prone) entries.

    Smartsheet Consulting, Solution Building, Training and Support.

    Projects for Processes and for People.

Sign In or Register to comment.