SUMIF using date ranges & referencing account numbers

L

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

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top