Welcome to the Smartsheet Forum Archives


The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

COUNTIF problems with data types

Options
Kent Vaughn
Kent Vaughn ✭✭✭✭
edited 12/09/19 in Archived 2017 Posts

( I have looked extensively to see what issue I'm having but cannot find an adequate answer ) 

 

=COUNTIF(StartDate:StartDate, MONTH(@cell) = 2)

 

Task: Just count the rows where a Date Field is FEB    ( we'll use StartDate as the field and it is a DATE field )    -----    The result I get is #INVALID DATA TYPE 

 

Why is SS indicating a problem here? 

 

https://app.smartsheet.com/b/publish?EQBCT=5da69588764148a1850fbebfe43d7e7b

 

 

 

 

Tags:

Comments

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭
    Options

    This one has me stumped.

    The error is coming from blank dates.

     

    This is not how it was working previously. I'm investigating (but am not a Smarsheet employee)

     

    Craig

  • Kent Vaughn
    Kent Vaughn ✭✭✭✭
    Options

    J. Craig  - Thanks!   Yes, I also concluded that if I excluded the blanks it seemed to work fine.   I joke to my team, "I love SS, I hate SS".  This weekend has been that example - trying to do what should be simple conditional logic ends up being an exercise in frusturation.   Makes it hard to convince others to migrate solutions to SS. 

     

    -- Kent 

     

     

  • Taylor F
    Taylor F Employee Admin
    Options

    Hello Kent, 

     

    Craig is correct by using the IFERROR. When the COUNTIF is checking the blank rows to see what month the cell is in, it will return a #INVALID DATA TYPE error. Placeing IFERROR around MONTH(@cell) and setting it to 0 will allow it to be skipped. 

     

    Taylor

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭
    Options

    Kent,

     

    Try this:

     

    =COUNTIF(StartDate:StartDate, IFERROR(MONTH(@cell), 0) = 2)

     

    My notes indicated this worked without the IFERROR() but I can not get it to again.

     

    Hope it gets better for you.

     

    Craig

     

     

     

This discussion has been closed.