Hi,

I have a long list of records all on the same worksheet. In column d are
the categories I want to search on (e.g. apples). I am tryoing to found out
how many apples between 01/01/2008 and 31/01/2008. I have tried

=SUMPRODUCT((D1:D999)=1/1/2008)*((D1:D999)<=31/12/2008)*(G1:G999="apples")

This returns 0 even though there are apples in column g between these dates.

Can somebody please help?

Best regards

Steve

Try it like this:
=SUMPRODUCT((D1:D999=--"1 Jan 2008")*(D1:D999<=--"31 Dec
2008")*(TRIM(G1:G999)="apples"))
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:16,400 Files:356 Subscribers:53
xdemechanik
---
"Stevo" wrote:
I have a long list of records all on the same worksheet. In column d are
the categories I want to search on (e.g. apples). I am tryoing to found out
how many apples between 01/01/2008 and 31/01/2008. I have tried

=SUMPRODUCT((D1:D999)=1/1/2008)*((D1:D999)<=31/12/2008)*(G1:G999="apples")

This returns 0 even though there are apples in column g between these dates.

Can somebody please help?

Best regards

Steve

Try it this way:

=SUMPRODUCT((D1:D999=--"1/1/2008")*(D1:D999<=--"31/12/2008")*(G1:G999="apples"))

or like this:

=SUMPRODUCT((D1:D999=DATE(2008,1,1))*(D1:D999<=DA TE(2008,12,31))*(G1:G999="apples"))

Hope this helps.

Pete

On Jul 29, 2:18*pm, Stevo wrote:
Hi,

I have a long list of records all on the same worksheet. *In column d are
the categories I want to search on (e.g. apples). *I am tryoing to found out
how many apples between 01/01/2008 and 31/01/2008. *I have tried

=SUMPRODUCT((D1:D999)=1/1/2008)*((D1:D999)<=31/12/2008)*(G1:G999="apples")

This returns 0 even though there are apples in column g between these dates. *

Can somebody please help?

Best regards

Steve

=SUMPRODUCT((D1:D999)=DATE(2008,1,1))*((D1:D999)< =DATE(2008,12,31))*(G1:G999="apples")

Regards,
Stefi

€žStevo€� ezt Ã*rta:

Hi,

I have a long list of records all on the same worksheet. In column d are
the categories I want to search on (e.g. apples). I am tryoing to found out
how many apples between 01/01/2008 and 31/01/2008. I have tried

=SUMPRODUCT((D1:D999)=1/1/2008)*((D1:D999)<=31/12/2008)*(G1:G999="apples")

This returns 0 even though there are apples in column g between these dates.

Can somebody please help?

Best regards

Steve

hi Stevo
Try
=SUMPRODUCT((D1:D999=--"1/1/2008")*(D1:D999<=--"31/12/2008")*(G1:G999="apples"))

Regards,
Pedro J.

Hi,

I have a long list of records all on the same worksheet. In column d are
the categories I want to search on (e.g. apples). I am tryoing to found out
how many apples between 01/01/2008 and 31/01/2008. I have tried

=SUMPRODUCT((D1:D999)=1/1/2008)*((D1:D999)<=31/12/2008)*(G1:G999="apples")

This returns 0 even though there are apples in column g between these dates.

Can somebody please help?

Best regards

Steve

=SUMPRODUCT(--((D1:D999)=Datevalue("1/1/2008")),--((D1:D999)<=Datevalue("31/12/2008")),--(G1:G999="apples"))
--
Hope this helps.
Thanks in advance for your feedback.
Gary Brown


"Stevo" wrote:

Hi,

I have a long list of records all on the same worksheet. In column d are
the categories I want to search on (e.g. apples). I am tryoing to found out
how many apples between 01/01/2008 and 31/01/2008. I have tried

=SUMPRODUCT((D1:D999)=1/1/2008)*((D1:D999)<=31/12/2008)*(G1:G999="apples")

This returns 0 even though there are apples in column g between these dates.

Can somebody please help?

Best regards

Steve

really appreciate the help - overwhelming response. I picked Pete Uk's
solution at random and it worked great. It seems that one of the differences
between Pete's suggestion and my original attempt was hte inclusion of dashes
(-----), can anybody please tell me what do these represent in the sum
product function?

Best regards

Steve

"Stevo" wrote:

Hi,

I have a long list of records all on the same worksheet. In column d are
the categories I want to search on (e.g. apples). I am tryoing to found out
how many apples between 01/01/2008 and 31/01/2008. I have tried

=SUMPRODUCT((D1:D999)=1/1/2008)*((D1:D999)<=31/12/2008)*(G1:G999="apples")

This returns 0 even though there are apples in column g between these dates.

Can somebody please help?

Best regards

Steve

The way you had it you were comparing D1:D999 with 1/1/2008, which Excel
interprets as 1 divided by 1 divided by 2008. To overcome this you need to
put 1/1/2008 within quotes, but then this will not be in the correct format.
The double unary minus, or --, converts this into a value, but you could
have used *1, or +0, or even VALUE( ... ) to convert it. The other formula I
gave you used the DATE function, as another way to achieve the result.

The other differences with your formula were the use of brackets.

Hope this helps.

Pete


"Stevo" wrote in message
...
really appreciate the help - overwhelming response. I picked Pete Uk's
solution at random and it worked great. It seems that one of the
differences
between Pete's suggestion and my original attempt was hte inclusion of
dashes
(-----), can anybody please tell me what do these represent in the sum
product function?

Best regards

Steve

"Stevo" wrote:

Hi,

I have a long list of records all on the same worksheet. In column d are
the categories I want to search on (e.g. apples). I am tryoing to found
out
how many apples between 01/01/2008 and 31/01/2008. I have tried

=SUMPRODUCT((D1:D999)=1/1/2008)*((D1:D999)<=31/12/2008)*(G1:G999="apples")

This returns 0 even though there are apples in column g between these
dates.

Can somebody please help?

Best regards

Steve