Sumif value is in year

Question

I have list of dates and values:

01.01.2016   100

01.02.2017   200

01.03.2017   300

What i want is now if the the year is 2017 count the value. Result: 200 + 300 = 500

This is a formula i tried:

=SUMIF($F5:$F,"<="&$A2,AF5:AF)

The issue is that google sheet can not compare a date and a year. When i change the date from 01.02.2017 to 2017 it works.

If i try to get the year of the date it doesnt work:

=SUMIF(YEAR($F5:$F),"="&$A2,AF5:AF)

How can compare a date with a year? Or is there an other way?

Thanks!

I’m voting to close this question because it is not related to programming. It should have been posted on [webapps.se] instead. — Didier L, Dec 22 '20 at 22:56
A bit of an old question, but found it while looking for the easiest solution and thought I'ld add that you can do this with sumif/sumifs easily. Just turn: =SUMIF(YEAR($F5:$F),"="&$A2,AF5:AF) Into: =arrayformula(SUMIF(YEAR($F5:$F),"="&$A2,AF5:AF)) — Mariano Molina, Jan 03 '22 at 19:29

score 27 · Accepted Answer · answered Jun 18 '17 at 10:03

27

Try this formula:

=SUM(FILTER(B:B,YEAR(A:A)=2017))

And also please try this:

=QUERY(A:B,"select year(A), sum(B) where A is not null group by year(A)")

answered Jun 18 '17 at 10:03

Max Makhrov

Thanks the first formula worked well but the second does not work. – Taaut Jun 19 '17 at 09:22
Thanks! `QUERY` is great for someone who knows SQL already. – ddevienne Apr 24 '23 at 14:28

1 Answers1