Skip to content
  • Try Smartsheet

    • 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.

    Can't seem to get this simple formula right.

    ✭✭✭✭✭✭
    in Formulas and Functions
    Screenshot 2022-09-09 at 1.22.24 PM.png

    {Weighted Forecast} is USD amount column

    {ExpectedClose} is a date column

    This formula in row 8 shows "Invalid Data Type"

    Screenshot 2022-09-09 at 1.22.14 PM.png

    For row 9, I am replacing {ExpectedClose} with {CreatedDate} and it works well.

    May I know what could be the error?

    {CreatedDate} in the source sheet is an Auto Date column

    {ExpectedClose} is manually input, however there are some blank rows. Could it be caused by the blank rows? How can I rectify this formula?

    Thanks.

    Vivien Chong

    Modern Work Management Consultant @ 57Network Consultancy Sdn Bhd

    Connect with us: 57network.com

    · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Best Answer

    • ✭✭✭
      Answer ✓

      Hi @Vivien Chong, I simulated your scenario - yes the problem is that there are some blank cells in there so MONTH(<blank>) will result in error. One idea how to make this work is to amend your formula as follows:

      =SUMIFS({Weighted Forecast}, {CreatedDate}, IFERROR(MONTH(@cell), 13) = Month@row,...)

      It worked in my simulation.

      Hope this helps...

      Vojtech

      · Share on FacebookShare on Twitter

    Answers

    • ✭✭✭
      Answer ✓

      Hi @Vivien Chong, I simulated your scenario - yes the problem is that there are some blank cells in there so MONTH(<blank>) will result in error. One idea how to make this work is to amend your formula as follows:

      =SUMIFS({Weighted Forecast}, {CreatedDate}, IFERROR(MONTH(@cell), 13) = Month@row,...)

      It worked in my simulation.

      Hope this helps...

      Vojtech

      · Share on FacebookShare on Twitter
    • ✭✭✭✭✭✭

      Thanks @Vojtech Gajdos. Your formula works.

      But I do not understand why are we putting a "13" in the IFERROR formula?

      Vivien Chong

      Modern Work Management Consultant @ 57Network Consultancy Sdn Bhd

      Connect with us: 57network.com

      · Share on FacebookShare on Twitter
    • ✭✭✭

      Hi @Vivien Chong, the reason for IFERROR(MONTH(@cell), 13) is: since MONTH(@cell) returns error (MONTH function requires a date and if blank cannot cope) we need to use IFERROR() and replace the error message by some number but since we don't want to include the row into the sum, it needs to be a number which will not match the month and that is 13 (or any number greater than 13) - since such month obviously doesn't exist.

      Hope it makes sense Vivien...

      If you are satisfied with the answer, kindly please mark your post as Answered

      Regards

      Vojtech

      · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    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