COUNTIF by MONTH in Sheet Summary Field

m_dorsey

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?

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

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

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.

caisser

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