1

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.

2

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)
  • thank you very much.. but is there a way not to mention > & < so I had to change the year range every time I move from year to another... – Hisham Aug 18 '14 at 21:28
  • I added a formula that you could store the start and end range in a cell. Alternatively you could also use the =year(A1) as a helper column and do a sumif on that column. I would probably go with teylyn's answer though as I like that better. – gtwebb Aug 18 '14 at 22:44
1

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)

enter image description here

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)

enter image description here

  • Thank you,the dates actually are not text, but I had to mention here in this way, it is dates. – Hisham Aug 18 '14 at 21:30
  • I've added a date scenario to my answer. – teylyn Aug 18 '14 at 21:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.