Excel SUMIF variable date range

2
votes

"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.

2
excelexcel-formulasumifs
Are the dates both date values or is one a text value and the other date value? They are date values if the dates change to numbers when you format them as 'General'. – Jerry
They are date values - I explicitly set them that way with format cells. – twelveFunKeys
Hey, erm, your references are incorrect by the way. Things like expenses!A2:expenses!A1024 should be actually expenses!A2:A1024 (sheet name is mentioned once for any range). Secondly, you don't need to mention the cell reference A2 in the condition, so try: =SUMIF(expenses!A2:A1024,"<="&main!$A$2,expenses!B2:B1024) (please type it all, and don't copy from here because the formula breaks on two lines and this will cause errors). GSpreadsheet example. – Jerry
The GSpreadsheet sample fixed it. Thanks! – twelveFunKeys

2 Answers

1
votes

You could also try:

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

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.