COUNTIF by MONTH in Sheet Summary Field

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

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_123
    L_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.

  • AaronM
    AaronM ✭✭✭
    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?

  • Stefan
    Stefan ✭✭✭✭✭✭

    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.

  • caisser
    caisser ✭✭

    Could anyone solve for this? I am having the same issue because of blank cells

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!