How to use SUMIF with Dates (specifically years) as Range and calculated values as Sum_Range
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

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)
Answers

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

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!

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?

Here's the formula:
=SUMIFS({Range 02}, {Range 01}, IFERROR(YEAR({Range 01}), 0) = [Reference Year]@2)

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)

The formula worked. Thank you very much, Paul!

Happy to help! 👍️
Help Article Resources
Categories
Check out the Formula Handbook template!