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

  • Carson Penticuff
    Carson Penticuff ✭✭✭✭✭✭

    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.

  • Carson Penticuff
    Carson Penticuff ✭✭✭✭✭✭

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!