SUMIF using date ranges & referencing account numbers

[lodicarter]

Hi folks,

My head is about to explode trying to figure this one out, so any help gratefully received!

I have 2 spreadsheets I am working with.

On no1 I have a list of contracts with account numbers (column D) and a start date (column H)and an end date (column I).

On no2 I have a list of payments (column L)referencing the same account numbers (column A)for various different start dates (column B) and end dates (column C). There are multiple payment lines for each account number.

I am currently using a SUMIF function in spreadsheet no1 to return the value of the payment on no2 (column L) when the account number on no1 (column D), matches the account number (column A) on no2. This works great and is as follows:

=SUMIF('[spreadsheet2]'$A:$A,D455,'[spreadsheet1]$L:$L)

What I would like to do in addition to the above, is ask it to only return the value in column L if the dates in columns B & C on spreadsheet 2 fall between the contract dates in columns H & I on spreadsheet 1.

If this makes sense to anyone and they have a solution, please send help!

Many thanks,

Losh.

 

[benmcclave]

Hello,

Here are two options:

=SUMPRODUCT((Sheet2!$L:$L)*(Sheet2!$A:$A=D455)*(Sheet2!$B:$B>=H455)*(Sheet2!$B:$B<=I455)*(Sheet2!$C:$C>=H455)*(Sheet2!$C:$C<=I455))


=SUMIFS(Sheet2!$L:$L, Sheet2!$A:$A, D455, Sheet2!$B:$B, ">="&H455, Sheet2!$B:$B, "<="&I455, Sheet2!$C:$C, ">="&H455, Sheet2!$C:$C, "<="&I455)

The first option is most flexible, as SUMIFS won't work with older versions of Excel.