SumProduct question
 

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.

SumProduct question
 

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.

"Jamesy" wrote in message
...
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.

SumProduct question
 

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))


"Andy" wrote:

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.

"Jamesy" wrote in message
...
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.

SumProduct question
 

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

Andy.

"Jamesy" wrote in message
...
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))


"Andy" wrote:

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.

"Jamesy" wrote in message
...
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.

SumProduct question
 

Andy your a superstar! Great stuff!

Many thanks for your effort, have a great weekend!

Regards,
James.

"Andy" wrote:

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

Andy.

"Jamesy" wrote in message
...
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))


"Andy" wrote:

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.

"Jamesy" wrote in message
...
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.

SumProduct question
 

Thanks for the feedback. Have a good weekend yourself!

Andy.

"Jamesy" wrote in message
...
Andy your a superstar! Great stuff!

Many thanks for your effort, have a great weekend!

Regards,
James.

"Andy" wrote:

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

Andy.

"Jamesy" wrote in message
...
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))


"Andy" wrote:

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.

"Jamesy" wrote in message
...
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.