need help adding year to a SUMIF formula.

kscott01

I have a cross sheet formula that sums activities based on "area" and displays in a dashboard. 

=SUMIFS({ITS Calendar rev3_WEEKS WEEKST}, {ITS Calendar rev3_WEEKS Activity}, ="Installation-Remisol", {ITS Calendar rev3_WEEKS Area}, ="West")

This formula is currently working. I need this same calculation to perform based on start date year. Currently, all the start dates are after 01/01/2022. Once we begin adding 2023, these are the only dates I want in the calculations to be used for a dashboard.

Here is what I have tried:

=SUMIFS(Start Date:Start Date, Year:Year, 2023, {ITS Calendar rev3_WEEKS WEEKST}, {ITS Calendar rev3_WEEKS Activity}, ="Installation-Remisol", {ITS Calendar rev3_WEEKS Area}, ="Central") - receive UNPARSABLE

=SUMIFS({ITS Calendar rev3_WEEKS WEEKST}, {ITS Calendar rev3_WEEKS Activity}, ="Installation-Remisol", {ITS Calendar rev3_WEEKS Area}, ="Central", {ITS Calendar rev3_WEEKS Start Date} this formula returns as #INVALID REF

Thanks,

Paul Newcome

You would use something like this:

=SUMIFS({ITS Calendar rev3_WEEKS WEEKST}, {ITS Calendar rev3_WEEKS Activity}, ="Installation-Remisol", {ITS Calendar rev3_WEEKS Area}, ="Central", {ITS Calendar rev3_WEEKS Start Date}, IFERROR(YEAR(@cell), 0) = 2023)

kscott01

thanks for your assistance. However, I am receiving this error:

Paul Newcome

I don't see an error in your screenshot.

kscott01

=SUMIFS({ITS Calendar rev3_WEEKS WEEKST}, {ITS Calendar rev3_WEEKS Activity}, ="Installation-Remisol", {ITS Calendar rev3_WEEKS Area}, ="Central", {ITS Calendar rev3_WEEKS Start Date}, IFERROR(YEAR(@cell), 0) = 2023)

Paul Newcome

You will need to follow the appropriate steps to create the cross sheet reference (the same way the other cross sheet references were made)