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.

    Issue when Converting to Column Formula - #Invalid Data Type

    ✭✭
    in Formulas and Functions

    I am working on a Smartsheet to track invoices for contractors and automatically update how much I need to accrue at the end of the month based on the contractor and average price. Each row in my sheet represents an invoice with details such as the contractor's name, invoice date, and price. I need to calculate the total charges for each contractor for a given month. Price- contains invoice amountm, Date - Week end date of period contractor worked and Identifier- Contractor name, the rest are formulas.

    Month Number =Month(Date@row)

    Year Number = Year(Date@row)

    Total Monthly Charges=SUMIFS([Price]:[Price], [Identifier]:[Identifier], [Identifier]@row, [Month Number]:[Month Number], [Month Number]@row, [Year Number]:[Year Number], [Year Number]@row)

    All of these work, until I convert to column formula, then my total monthly charges shows an error.

    I have tried to do =Value(Month(Date@row) and same with year, but it is still showing either #INVALID DATA TYPE or #INVALID COLUMN VALUE. All columns except the Date column are Text/Number.

    Does anyone know why this is happening?

    Tags:
    · Share on FacebookShare on Twitter

    Best Answer

    • ✭✭✭✭✭
      Answer ✓

      Add an IFERROR statement to the conversion of the months to numbers. If you have a blank date in your data set that will give you an error which will show up in your Total Charges causing it to also error when you try to make it a column formula.

      Month Number =Month(Date@row) → =IFERROR(MONTH(Date@row), "")

      Year Number = Year(Date@row) → =IFERROR(YEAR(Date@row), "")

      · Share on FacebookShare on Twitter

    Answers

    • ✭✭✭✭✭
      Answer ✓

      Add an IFERROR statement to the conversion of the months to numbers. If you have a blank date in your data set that will give you an error which will show up in your Total Charges causing it to also error when you try to make it a column formula.

      Month Number =Month(Date@row) → =IFERROR(MONTH(Date@row), "")

      Year Number = Year(Date@row) → =IFERROR(YEAR(Date@row), "")

      · Share on FacebookShare on Twitter
    • ✭✭

      Yes, that worked. Thank you!

      · 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