Smartsheets Formula Error Code
Hello Smartsheets Community,
I have this code and it is resulting in an "Invalid Data Type" error. The code should display the number of rows which the following column criteria applies:
Column 1 Look for the letter "N", column 2 look for the number "1", and column 8 looking to see if it is in august. My formula is below:
=COUNTIFS({CBOM - Yellowstone Range 1}, "N", {CBOM - Yellowstone Range 2}, 1, {CBOM - Yellowstone Range 8}, MONTH(@cell) = 9)
Any help to get this working would be very appreciated! Thanks!
Answers
-
If any cells in {CBOM - Yellowstone Range 8} are not formatted as dates, this error will be the result. It is possible one or more cells are not formatted correctly, but it usually because there is either a blank cell, or may blank rows at the bottom of the sheet (those count too).
The easiest solution is to throw an IFERROR() around the MONTH() statement.
=COUNTIFS([CBOM - Yellowstone Range 1]:[CBOM - Yellowstone Range 1], "N", [CBOM - Yellowstone Range 2]:[CBOM - Yellowstone Range 2], 1, [CBOM - Yellowstone Range 8]:[CBOM - Yellowstone Range 8], IFERROR(MONTH(@cell), 0) = 9)
You may want to manually verify you are receiving the expected output, just to make sure all of your dates are indeed formatted correctly.
-
@Carson Penticuff Thank you for the help. Unfortunately, when I used that code, I got a new error message below:
Not sure what is causing this. I am verifying the dates are formatted correctly but I believe that they are.
-
Wow, I can see why you were getting the error. I changed the formula around to test it and forgot to change it back for you setup. This should work much better:
=COUNTIFS({CBOM - Yellowstone Range 1}, "N", {CBOM - Yellowstone Range 2}, 1, {CBOM - Yellowstone Range 8}, IFERROR(MONTH(@cell), 0) = 9)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!