sumproduct between dates

[Johnny M]

Hi
I have three columns and I want to add up the quantity of product abc1 that
was sold in October. I am trying the following formula where B5 is product
abc1, D1 is a cell with 10/1/06 in and D2 is a cell with 10/31/06 in:
=SUMPRODUCT(($A$2:$A$8=B5)*($c$3:$c$8>=$D$1<$D$2)*($b$3:$b$8)).
Currently it is just returning zeros.....

Product Quantity Date
abc1 50 10/20/06
abc2 60 3/21/06
abc3 70 6/20/06
abc1 80 10/3/06
abc1 90 11/11/06
abc2 100 11/12/06

Any help will save my job!

 

[Roger Govier]

Hi Johnny

Try
=SUMPRODUCT(($A$2:$A$8=B5)*
(TEXT($C$3:$C$8,"yyymm")=TEXT($D$1,"yyyymm")*($B$3:$B$8)).

Using your formula you would need to have amended it to
=SUMPRODUCT(($A$2:$A$8=B5)*($c$3:$c$8>=$D$1)
*($c$3:$C$8<$D$2)*($b$3:$b$8)).

The tests for >= to one date and < another date would have to be
separate tests within the formula
The first formula selects the correct year and month on a single test,
but will not work if you want dates spanning a month end.

 

[Johnny M]

Roger - many thanks. Your formula still returns zero for some reason, but
your version of my old formula does the trick! I'll see if I can now apply it
to the actual model. Thanks once again....

 

[Roger Govier]

Hi Johnny

It would help if I could see!!!

Didn't type enough y's in the first part of the formula so the
comparison wouldn't have worked.
Should be
=SUMPRODUCT(($A$2:$A$8=B5)*
(TEXT($C$3:$C$8,"yyyymm")=TEXT($D$1,"yyyymm")*($B$3:$B$8)).

Glad you got it sorted anyway, and thanks for the feedback.