Excel SUMIF variable date range

Question

"main" is a worksheet with a "Week ending" column in it. "expenses" is a worksheet with expense figure in column B and specific dates in column A.

I want to sum the expenses that occur on or up to 7 days before the main Week ending date.

Following the examples here: https://www.ablebits.com/office-addins-blog/2014/11/04/excel-sumif-function-formula-examples/

I came up with this

=SUMIF(expenses!A2:expenses!A1024,main!$A$2&"<="&expenses!$A$2,expenses!B2:expenses!B1024)

But it gives me zero.

EDIT

The worksheets look like this:

Main

|A          |B    |C    |D    |E    |F        |
|Week Ending|     |     |     |     |Expenses |
|16/11/2014 |     |     |     |     |formula goes here = should total 100|
|23/11/2014 |     |     |     |     |formula goes here = should total 25|

Expenses

|A          |B    |
|Date       |Value|
|10/11/2014 |5.00|
|11/11/2014 |20.00|
|12/11/2014 |15.00|
|12/11/2014 |10.00|
|10/11/2014 |50.00|
|17/11/2014 |5.00|
|18/11/2014 |20.00|

This works if I just put the week ending date in the expenses sheet:

=SUMIF(expenses!A2:expenses!A1024, main!a2, expenses!b2:expenses!b1024)

But I can't change the dates because they have to match the receipts. So I tried using AND:

=SUMIF(expenses!A2:A1024, AND("<="&main!A2, ">"&main!A2-7), expenses!A2:expenses!A1024)

Still zero.

@pnuts

I tried your SUMIFS suggestion but I still get zero.

Answer 1

Because I think you need two conditions (one for each end of each week) I suggest SUMIFS:

=SUMIFS(Expenses!A2:A1024,Expenses!B2:B1024,"<="&Main!C2,Expenses!B2:B1024,">"&Main!C2-7)  

This might be simplified if the results are in Main (eg next to the Week ending values, assumed to be in ColumnC) by not specifying that sheet.

If obliged to resort to SUMIF you might add up to the week ending date with one part of the formula and deduct up to the previous weekending date with the other part.

Answer 2

You could also try:

=SUMPRODUCT((expenses!$A$1:$A$1024>=main!$A$2-7)*(expenses!$B$1:$B$1024))