Sheet formula Error Message
I have a column titled "Current Month", this column is comprised of checkboxes, I have the following formula automatically checking the box if it is one month prior (for auditing purposes); for example if the Date is 10/08/2024, I need to check all boxes where the date starts with 09.
=IF(AND(MONTH([Date of Transportation]@row) = MONTH(TODAY()) - 1, YEAR([Date of Transportation]@row) = YEAR(TODAY())), 1, 0)
With that said I am building a dashboard and utilizing sheet summaries to fill it.
I am trying to Count all checkboxes that are checked in the "Current Month" Column.
I have tried =COUNTIF([Current Month]:[Current Month], 1) but it is not working and bringing "INVALID DATA TYPE"
by using these formulas I am hoping for everything to automatically update every month so I don't manually have to change it on the dashboard.
Answers
-
Your COUNTIF formula seems to be fine, I see no issue there. It may be possible that you have one or more entries in the [Date of Transportation] column that are not formatted as dates. Do you have the "Restrict to Dates Only" slider for that column enabled? It may help to include an IFERROR statement on your checkbox column formula.
If this sheet overlaps from year to year, you will have issues with the formula. The following formula will handle overlapping years and also includes the IFERROR statement.
=IFERROR(IF(AND(MONTH([Date of Transportation]@row) = MONTH(TODAY() - DAY(TODAY())), YEAR([Date of Transportation]@row) = YEAR(TODAY() - DAY(TODAY()))), 1, 0), "")
-
It works now! Thank you for your help.
-
Awesome, I'm glad it worked for you!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.6K Get Help
- 403 Global Discussions
- 215 Industry Talk
- 455 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 56 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!