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

Peter Hui

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.

Paul Newcome

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)

Paul Newcome

Try this...

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

Peter Hui

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!

Paul Newcome

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?

Peter Hui

Here's the formula:

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

Paul Newcome

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)

Peter Hui

The formula worked. Thank you very much, Paul!

Paul Newcome

Happy to help! 👍️