vlook up to sum between start and end dates.

[NinjaBear]

On tab "Letters" is a simple table that column A is "Date Letter Sent" and column B is the total number of letters sent on the date listed in column A.
(If it is important, not every day will be listed. So Column A can go 9/3/13,9/4/13,9/10/13,9/12/13,9/13/13 etc. If any letters are sent, the date and number of letters is populated. If no letters are sent, the date is not listed. So skipped dates and no null or 0 values on letters sent. But the dates are kept in sequential first to last order.)

On tab "results" is a table that column A is "Start Date" and Column B is "End Date" and column C is "Total Letters sent between start and end date."

Currently it is a manual process to sum the number of letters sent between the two dates and type in the total. But it seems that I should be able to turn it into a calculated column that automatically looks up all the dates between the start and end dates and sums the total.

I think it would have to use an array of CSE formula. But I can't quite get it to work.

Any help?

 

[Aladin Akyurek]

Looks like...

=SUMIFS(letters!B:B,Letters!A:A,">="&A2,Letters!A:A,"<="&B2)

 

[jackhandey]

***edit*** I must be getting tired. SUMIFS makes so much more sense. Ugh.


I think I would use the offset formula to set your range:

Put this in C1 and copy down...it's a little ugly, but based on my tests, it works.

=IFERROR(SUM(OFFSET(INDIRECT(ADDRESS(IF(COUNTIF(Letters!A:A,A1)=1,MATCH(A1,Letters!A:A,0),MATCH(A1,Letters!A:A)+1),2,,,"Letters")),0,0,IF(COUNTIF(Letters!A:A,B1)=1,MATCH(B1,Letters!A:A,0),MATCH(B1,Letters!A:A))-IF(COUNTIF(Letters!A:A,A1)=1,MATCH(A1,Letters!A:A,0),MATCH(A1,Letters!A:A)+1)+1)),"")

My Indian food is ready to pick up, so instead of explaining why I did what I did, I'm going to go pick it up let me know if this doesn't work or is unclear.

Cheers!