Hi,

I am really struggling with a foruma for a spreadsheets used by our
warehouse department, I'm sure i am nearly there and have read all threads
regards the use of between dates etc.

I need to create a formula to return the value of a cell if 2 conditions are
met, the first - Product Code and the second between two dates i.e. between
1/1/09 and 31/1/09 if both are true to enter the qty in the spreadsheet from
another worksheet.

I hope this makes sense, I have three separtate worksheets, and this is the
sort of thing i've been experimenting with:

=SUMPRODUCT(--(Allocations!E2:E2001DATE(2009,1,3)),--(Allocations!E2:E2001<DATE(2009,31,3)),--(Allocations!B2:B629=Keystage!B2),(Allocations!F2: F2001))

I think this is wrong and am not getting any result.

I'm sure it is a SUMIF or just an IF statement I need???

Any help would be appreciated.

Basically,

If PRODUCT CODE matches, and is between the two dates, return the quantity
in the quantity column, if not put zero.

Sounds easy.......LOL

Please help. Ang.

Your formula modified
=SUMPRODUCT(--(Allocations!E2:E2001DATE(2009,1,3)),--(Allocations!E2:E2001<DATE(2009,31,3)),--(Allocations!B2:B2001=Keystage!B2),(Allocations!F2 :F2001))

OR

Try the below..(untested)

=SUMPRODUCT(--(TEXT(Allocations!E2:E2001,"MM")="03"),
--(Allocations!B2:B2001=Keystage!B2),Allocations!F2: F2001)

or

=SUMPRODUCT(--(MONTH(Allocations!E2:E2001)=3),
--(Allocations!B2:B2001=Keystage!B2),Allocations!F2: F2001)


--
If this post helps click Yes
---------------
Jacob Skaria


"Ang" wrote:

Hi,

I am really struggling with a foruma for a spreadsheets used by our
warehouse department, I'm sure i am nearly there and have read all threads
regards the use of between dates etc.

I need to create a formula to return the value of a cell if 2 conditions are
met, the first - Product Code and the second between two dates i.e. between
1/1/09 and 31/1/09 if both are true to enter the qty in the spreadsheet from
another worksheet.

I hope this makes sense, I have three separtate worksheets, and this is the
sort of thing i've been experimenting with:

=SUMPRODUCT(--(Allocations!E2:E2001DATE(2009,1,3)),--(Allocations!E2:E2001<DATE(2009,31,3)),--(Allocations!B2:B629=Keystage!B2),(Allocations!F2: F2001))

I think this is wrong and am not getting any result.

I'm sure it is a SUMIF or just an IF statement I need???

Any help would be appreciated.

Basically,

If PRODUCT CODE matches, and is between the two dates, return the quantity
in the quantity column, if not put zero.

Sounds easy.......LOL

Please help. Ang.

Hi Jacob,

Thanks for your solution, sorry its taken me ages to get back to you. I
couldn't get the formula you suggested to work, however, I have taken yours
as an example and managed to get it to work by adding another column to get
the number month first (=month), then using this within SUMPRODUCT as well as
it search for a specific text and this is working a treat. The only problem
is the setting up, over 2000 entries and each formula has to be adjusted to
search for that specific text or month number. I suppose once it is done it
will save hours of manual work!

Thank so much for your time.

--
Angela


"Jacob Skaria" wrote:

Your formula modified
=SUMPRODUCT(--(Allocations!E2:E2001DATE(2009,1,3)),--(Allocations!E2:E2001<DATE(2009,31,3)),--(Allocations!B2:B2001=Keystage!B2),(Allocations!F2 :F2001))

OR

Try the below..(untested)

=SUMPRODUCT(--(TEXT(Allocations!E2:E2001,"MM")="03"),
--(Allocations!B2:B2001=Keystage!B2),Allocations!F2: F2001)

or

=SUMPRODUCT(--(MONTH(Allocations!E2:E2001)=3),
--(Allocations!B2:B2001=Keystage!B2),Allocations!F2: F2001)


--
If this post helps click Yes
---------------
Jacob Skaria


"Ang" wrote:

Hi,

I am really struggling with a foruma for a spreadsheets used by our
warehouse department, I'm sure i am nearly there and have read all threads
regards the use of between dates etc.

I need to create a formula to return the value of a cell if 2 conditions are
met, the first - Product Code and the second between two dates i.e. between
1/1/09 and 31/1/09 if both are true to enter the qty in the spreadsheet from
another worksheet.

I hope this makes sense, I have three separtate worksheets, and this is the
sort of thing i've been experimenting with:

=SUMPRODUCT(--(Allocations!E2:E2001DATE(2009,1,3)),--(Allocations!E2:E2001<DATE(2009,31,3)),--(Allocations!B2:B629=Keystage!B2),(Allocations!F2: F2001))

I think this is wrong and am not getting any result.

I'm sure it is a SUMIF or just an IF statement I need???

Any help would be appreciated.

Basically,

If PRODUCT CODE matches, and is between the two dates, return the quantity
in the quantity column, if not put zero.

Sounds easy.......LOL

Please help. Ang.