Issue when Converting to Column Formula - #Invalid Data Type

nicoleanew

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?

Devin Lee

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), "")