Date column not working in formula

Options

I have a formula working fine: =SUMIF({Install Date}, MONTH(@cell) = 4, {MFD Count})

And one giving #INVALID DATA TYPE: =SUMIF({Final Install Date}, MONTH(@cell) = 4, {MFD Count})

The only difference is the reference date column. Both are set to Column Type: Date and neither has date restriction checked. Any idea what else could be making this happen? The formulas are identical besides the date column.

Answers

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    Options

    @Suz what about the column you are bringing the information to? as well as the date column in your ref sheet.

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

  • Suz
    Options

    I have a formula sheet that I use for my dashboard so the formulas are not in the sheet they're referencing. The columns w/formulas are regular text columns, both the working one and broken one.

    Date column in ref sheet is set to Date, not restricted. Just like the one that's working.

    I just tried copying the dates, clearing contents in SS, pasted the dates in Excel, formatting to date, copied back into SS and refreshed and still same error.

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    Options

    Some times sumif and countif functions wont work until you save and refresh the sheet. See what that does for you.

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

  • Suz
    Options

    refreshed and saved several times. just can't figure this out.

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    Options

    have you tried rebuilding the formula. I don't know if you copy and pasted. only changing the reference or not. I have seen smartsheet do some really awkward things when it comes to copying and pasting.

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!