SumProduct question

[Guest]

Help!

I can't seem to get this simply formula to work!

Heres an example....

Col A Col B Col C
Green Tea £1.00 1st January 2006
Green Tea £1.00 25th October 2005
Green Tea £1.00 5th January 2006
Red Tea £2.00 8th February 2006
White Tea £3.00 20th December 2005


Now in short what I require is a function to look up "Green Tea", then ONLY
return the product price value if equal to or greater than 1st January 2006,
so the answer would be £2.00.

Your help again is always appreciated.

Thanks,
James.

 

[Guest]

Hi Jamesy

Does column C contain Excel dates, or are the entries text? If they are
dates, then try this:
=SUMPRODUCT(--(A2:A6="Green Tea"),--(C2:C6>=DATE(2006,1,1)),--(B2:B6))

Andy.

 

[Guest]

Many thanks Andy!

This works perfectly for my mini example, but when putting this formula into
a master spreadsheet it brings back N/A!

In answer to your first question the "date" field are actual dates and not
text. Any ideas why this may be bringing back N/A?? Below is my current
formula, column C contains product names, column F contains dates and column
G contains the values. I'm completely baffled here!!


=SUMPRODUCT(--(Stats!$C$5:$C$17652=B4),--(Stats!$F$5:$F$17652>=DATE(2006,1,1)),--(Sheet1!$G$5:$G$17652))

 

[Guest]

Maybe you have a #N/A in one of your ranges.

Andy.

 

[Guest]

Andy your a superstar! Great stuff!

Many thanks for your effort, have a great weekend!

Regards,
James.

 

[Guest]

Thanks for the feedback. Have a good weekend yourself!

Andy.