Excel SUMIF using text and date ranges defined by cell value

Question

What I have:

I have two spreadsheets in a workbook titled "Ledger" and "Budget".
"Budget" uses SUMIF on "Ledger" to find the total "Weekly rent".

"Ledger" spreadsheet:

"Ledger" spreadsheet

"Budget" spreadsheet:

"Budget" spreadsheet

What I need:

I need the SUMIF function to simultaneously work with a date range. e.g. In the "Budget" spreadsheet, the desired SUMIF value (Cell O8) should be constrained by "Week start" (Cell B8) and "Week end" (Cell C8). Right now, as depicted in the screenshots, the SUMIF does not apply the date range criteria.

My code:

For cell O8:

=SUMIF(Ledger!M:M, "Weekly rent", Ledger!P:P)

My question:

How do I include the date criteria using "Week start" (Cell B8) and "Week end" (Cell C8)?

If you are using Excel 2007 or later, use SUMIFS which can use more than 1 condition. Otherwise use SUMPRODUCT. There are plenty of examples of both on SO — chris neilsen
@chrisneilsen - Thanks. Ì found a solution based on your recommendations. The answer is outlined below. — Clarus Dignus

Clarus Dignus Clarus Dignus · Accepted Answer · 2014-05-17T23:01:01

Code:

=SUMIFS(Ledger!P:P, Ledger!M:M, "=Weekly rent", Ledger!J:J, ">="&B8, Ledger!J:J, "<="&C8)

As suggested by user chris neilsen, I've used the SUMIFS function to apply multiple conditions.

Break-down:

Ledger!P:P, = the column containing the values that are to be summed (added) together.

Ledger!M:M, = the column that to search for iterations of "Weekly rent" in.

"=Weekly rent", = Condition #1: Cell value equals "Weekly rent".

Ledger!J:J, ">="&B8, = Condition #2: The value of the date (i.e. cell in column J that's on the same row to the corresponding cell in column M) is greater than or equal to the start dates in B8.

Ledger!J:J, ">="&C8, = Condition #3: The value of the date (i.e. cell in column J that's on the same row to the corresponding cell in column M) is less than or equal to the end dates in C8.