summing up numbers when criteria match

Question

I have a range of long dates from Sep 2014 till Dec 2018, and for each month I have an amount. I want to sum up the data of each year in one cell.

Example:

• 2014 : sum of all amounts that are in 2014
• 2015 : sum of all amounts that are in year 2015

Sep2014 oct2014 Nov2014 Dec2014 Jan2015 Feb2015 ... 100 200 250 150 20 50

I know that 2014 = 100+200+250+150 = 700, but I need a formula to search for all cells that include that year and sum up the numbers.

Answer 1

You can just use a sumifs function

=SUMIFS(B1:B100,A1:A100,">=" & DATE(2014,1,1),A1:A100,"<" & DATE(2015,1,1))

Where A1:100 has your dates and B1:B100 has your values. Checks if its greater or equal to jan 1, 2014 and less then jan 1, 2015.

This assumes your values are stored as proper dates and not just text values.

If the start and end range are stored in cells use something like

=SUMIFS(B1:B100,A1:A100,">=" & F1,A1:A100,"<" & G1)

Answer 2

If you don't want to change the values in the first row from Text to real dates, you can use a Sumproduct() formula

=SUMPRODUCT(--(RIGHT($A$1:$F$1,4)+0=A4),$A$2:$F$2)

If the data is in fact real dates, then you can sum based on the year of the date with

=SUMPRODUCT(--(YEAR($A$1:$F$1)=A4),$A$2:$F$2)