How to use SUMIF with Dates (specifically years) as Range and calculated values as Sum_Range

Peter Hui
Peter Hui
edited 12/15/20 in Formulas and Functions

Hello everyone!

Good day.

I'm trying to come up with a SUMIF formula where the Range is a collection of Dates (specifically Years) and the Sum_Range consists of calculated values. The criterion is also a calculated value. However, I keep getting the error "Invalid Data Type".

=SUMIF(YEAR({Range 01}), =[Reference Year]2, VALUE({Range 02}))

I checked the data type assignment for each column and aside for the Effective Date in the reference sheet, which is in date format, all the rest are in Text/Number format.

Below are couple of screenshots for reference.

Hope you could help me figure this out.

Thank you.

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    You need to use "@cell" inside of the YEAR function, and you should use either "@row" or "2" for the row number.

    @cell tells the function to evaluate each cell within the range on an individual basis, and @row tells the formula to evaluate the column on whichever row the formula is on.

    =SUMIFS({Range 02}, {Date Range 01}, IFERROR(YEAR(@cell), 0) = [Reference Year]@row)


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!