COUNTIF by MONTH in Sheet Summary Field

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?

Find more posts tagged with

Formulas

Project Management

Comments

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

Sign in to join the conversation!

Quick Links

Trending discussions

Date Column 1 < Gate Column 2 = At Risk
I have a Target Date column and the typical Finish column. I am looking for a formula that will tell me if Target Date is less than Finish Date mark the cell AT RISK. But if the Target Date cell is blank leave it blank. =IF([Target Date]@row > [Finish]@row , "Yes", "AT RISK") I can't get the ISBLANK to work.
MM/DD/YYYY Formula vs. TODAY
I am new to formulas and looking for a way to populate the "How Many Years?" column on my sheet vs. today's date. Thank you in advance!
Formula to Capture Total Days Open with Status Changes
I hope that everyone is well! :) I have a use case where I need a formula that allows me to capture the number of days a request has been open but stopping and starting the clock when the status changes. From: In Progress to On Hold to In Progress to Closed. Also, I would only like to count weekdays. (No weekends or…