Hi: I am trying to generate a sum (cumulative) of all effort "points" for projects started in the current year.
CASE 1: My reference columns are: "Project Start" (date) and "Effort Count (Est. Total)" (text/number).
I used the formula: =SUMIF([Project Start]:[Project Start], YEAR(@cell) = 2020, [Effort Count (Est. Total)]:[Effort Count (Est. Total)]). But this returned #INVALID DATA TYPE.
CASE 2: I used the same syntax to calculate the sum of all project costs for projects started in the current year.
My reference columns are: "Project Start" (date) same as above and "Upcharge Amt" (text/number)
I used =SUMIF([Project Start]:[Project Start], YEAR(@cell) = 2020, [Upcharge Amt]:[Upcharge Amt]). This formula worked fine and returned a result that was about what I anticipated.
My question is:
(1) What is wrong for the CASE 1 Effort Count formula that it is returning #INVALID DATA TYPE?
(2) How can I update the formulas in both CASE 1 and CASE 2 so that it is referencing the CURRENT year, rather than 2020? I don't want to have to update the formulas every year!
Thanks for your help!