Sum Product between dates

[Mike Baker]

I have a data set that comprises of 3 columns. Col A - Purchase order numbers (text), Col B - Date, Col C - Value. I have two reference dates in separate cells and two reference amounts in two other cells.

I need to interrogate the data and report the number of Purchase Orders between specific dates having values between the two reference amounts.

I have tried Sumproduct but without success so far. Would appreciate any help out there.

Thanks.

Mike

 

[ClimoC]

I had a similar problem once but did manage to get it to work.

I don't have access to the drive with the file anymore, but if my memory serves me correctly...

Assume that 'Start Date' is B1, 'End Date' is C1, D1:D1000 is your item date, and E1:E1000 is what you are summing..

Here goes...

{=Sumproduct(--($B$1<=D1:D1000),--($C$1>=D1:D1000),E1:E1000)}

FYI - JIC you don't know, you have to Ctrl+Shift+Enter on an array formula for it to work, which is how you get the "{ }" around the formula

 

[Andrew Poulsom]

Example:

=SUMPRODUCT(--(B2:B11>=D1),--(B2:B11<=D2),--(C2:C11>=E1),--(C2:C11<=E2))

 

[cornflakegirl]

Sumproduct doesn't need to be entered with Ctrl-Shift-Enter.

 

[ClimoC]

Sumproduct doesn't need to be entered with Ctrl-Shift-Enter.

No, but an Array formula Does, which is what I gave him

 

[ClimoC]

Example:

=SUMPRODUCT(--(B2:B11>=D1),--(B2:B11<=D2),--(C2:C11>=E1),--(C2:C11<=E2))

Whoops - yes Do what Andrew said. I missed the bit about the 'Value' Criteria as well as the date criteria.

 

[cornflakegirl]

No, but an Array formula Does, which is what I gave him

No, you gave a straightforward SUMPRODUCT formula, very similar in construction to Andrew's, which, you will notice, does not need array-entering.

 

[Mike Baker]

Thanks to all so far,

However, I have tried entering the formula that Andrew submitted both normally and as an array but it does not not compute correctly. For example, if I set the price criteria to zero, quantities of purchase orders still get reported even though there are none with a zero value. These numbers do change with differing values but they are not accurate.

 

[Andrew Poulsom]

My formula worked when I tried it. If it doesn't work for you, post some sample data, your formula and the expected result.

 

[Mike Baker]

Thanks again and here is a simplified copy of my data


No Date Supplier Net Low Value High Value
7561 07/07/2009 Supplier A £749 £10 £5,000
7720 14/07/2009 Supplier B £190
7721 14/07/2009 Supplier A £258 Date Range # PO's within Date and Value Range
7755 23/07/2009 Supplier B £105 Lo Hi
7756 23/07/2009 Supplier A £175 01/07/2009 31/07/2009 26
7757 24/07/2009 Supplier B £196 01/08/2009 31/08/2009 19
7758 27/07/2009 Supplier A £123 01/09/2009 30/09/2009 10
7766 03/08/2009 Supplier C £840
7767 04/08/2009 Supplier B £5,789
7780 13/08/2009 Supplier A £170
7781 13/08/2009 Supplier C £90
7782 13/08/2009 Supplier B £75
7793 17/08/2009 Supplier C £24
7794 17/08/2009 Supplier B £176
7795 18/08/2009 Supplier A £1,264
7811 01/09/2009 Supplier C £227
7812 01/09/2009 Supplier B £235
7813 01/09/2009 Supplier A £42
7814 01/09/2009 Supplier C £269
7815 02/09/2009 Supplier B £30