Which formula should I use to add column E values, grouped by year?

Question

For example, for year 2023, that formula should return sum of E3:E4, and when i drag down that formula, it should return, for 2022, sum of E5:E8.

=SUMIF(B2:B162, YEAR(B2)=2023, E2:E162)

I tied this formula for the year 2023 only, but this didnt returned the desired answer.

Answer 1

You can't use the Year-function within SumIf

Use Sumproduct instead like this:

=SUMPRODUCT($E$2:$E$162 * (YEAR($B$2:$B$162) = H2))

It will sum all cells from E whenever Year of accordin`g payment date equals the value from H2.

Answer 2

Rather than use the SUMIF function, I would recommend using the SUMIFS function, which accepts multiple criteria.

N.B. SUMIFS moves the range to be summed to become the first argument, and makes it Mandatory — unlike SUMIF where it was the last argument, and was Optional.

=SUMIFS($E$2:$E$162, $B$2:$B$162, ">=" & DATE($H2, 1, 1), $B$2:$B$162, "<" & DATE($H2+1, 1, 1))

"Sum the values in Column E, where Column B is on-or-after 1st January of this year, and Column B is before 1st January of next year"