Search code examples
exceldateworksheet

Sum between a range of dates from two different worksheets

I have two worksheets, let's name it Apple and Banana. I want to see the answers in Apple and get the values from Banana with the criteria January 1 - 15, 2016 only.

Given:

  • A2:A3000 with the dates
  • E2:E3000 the amount or expenses

My formula is:

=SUMIFS('Banana'!E2:E3000,'Banana'!A2:A3000,">="&DATE(2016,1,1),'Banana'!E2:E3000,"<"&DATE(2016,1,15))

It is adding the amount, but the problem is, it is also adding amounts greater than and equal to January 16.

Also, do you think it has something to do with the formatting of my dates in Banana? It is formatted as mm,dd,yy. But, if I put &Date, it requires year,month,day.

Solution

  • Argument 5 should be should be Banana!A2:A3000 (i.e. same as Argument 2) not Banana!E2:E3000 as you have it, because it must be the date column (A) not the value column (E).