Skip to content
  • Try for free

    • Formulas and Functions

    Formulas and Functions

    Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

    #INVALID DATA TYPE issue in SUMIFS formula caused by blank dates

    ✭✭
    in Formulas and Functions

    I have a SUMIFS formula that I use to calculate monthly dollar amounts from another sheet. I know I'm getting #INVALID DATA TYPE due to blank date columns in the source sheet. I think perhaps an IFERROR formula might take care of the issue but I cant get it formatted correctly.


    Current Formula. Returns correct result as long as the date fields in the source sheet are popultaed.

    =SUMIFS({Purchasing Log_FM0052 Rev. B Range 2}, {Purchasing Log_FM0052 Rev. B Range 1}, @cell = $Supplier@row, {Purchasing Log_FM0052 Rev. B Range 3}, MONTH(@cell) = 11, {Purchasing Log_FM0052 Rev. B Range 3}, YEAR(@cell) = 2021)

    Would the IFERROR work here and if so, how should it be put into this formula?

    · Share on FacebookShare on Twitter

    Best Answer

    • Community Champion
      Answer ✓

      Hey @Chris Benskin

      This should work for you

      =SUMIFS({Purchasing Log_FM0052 Rev. B Range 2}, {Purchasing Log_FM0052 Rev. B Range 1}, @cell = Supplier@row, {Purchasing Log_FM0052 Rev. B Range 3}, IFERROR(MONTH(@cell),0) = 11, {Purchasing Log_FM0052 Rev. B Range 3}, IFERROR(YEAR(@cell),0) = 2021)

      Does this get you what you need?

      Kelly

      · Share on FacebookShare on Twitter

    Answers

    Sign in
    Try for free

    Help Article Resources

    Create and edit formulas in Smartsheet

    Formula combinations for cross sheet references

    Smartsheet functions list

    Quick Links

    Categories

    Want to practice working with formulas directly in Smartsheet?

    Check out the Formula Handbook template!

    Trending in Formulas and Functions