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

Options
edited 12/15/20

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.

• ✭✭✭✭✭✭
Options

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)

• ✭✭✭✭✭✭
Options

Try this...

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

• Options

Hi Paul!

Thank you for sharing your inputs.

I tried the formula and I got the "Unparseable" error message. Here's what I did:

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

where

Range to sum = Final Cost column (Range 02 in the diagram)

Date Range = Effective Date column (Range 01 in the diagram)

YEAR(@cell) = YEAR(Range 01)

[Reference Year]@row = [Reference Year]2

Please let me know if I misunderstood/misused the formula.

Thanks!

• ✭✭✭✭✭✭
Options

The formula that you have posted above should not be throwing that particular error unless you column name is not actually "Reference Year".

If that is in fact you correct column name, can you copy/paste the exact formula directly from your sheet to here?

• edited 12/15/20
Options

Here's the formula:

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

• ✭✭✭✭✭✭
Options

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)

• Options

The formula worked. Thank you very much, Paul!

• ✭✭✭✭✭✭
Options

Happy to help! 👍️

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!