CODEWITHSUNDEEP
excelexcel-formulasumifs
stardotstar
stardotstar

Reputation: 318

Excel SUMIF variable date range

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

Upvotes: 2

Views: 1578

Answers (2)

Donut_AU
Donut_AU

Reputation: 46

You could also try:

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

Upvotes: 1

pnuts
pnuts

Reputation: 59442

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.

Upvotes: 0

Related Questions