Counting Records Between Two Dates

[Stevo]

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((D1999)>=1/1/2008)*((D1999)<=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

 

[Max]

Try it like this:
=SUMPRODUCT((D1999>=--"1 Jan 2008")*(D1999<=--"31 Dec
2008")*(TRIM(G1:G999)="apples"))
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:16,400 Files:356 Subscribers:53
xdemechanik

 

[Pete_UK]

Try it this way:

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

or like this:

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

Hope this helps.

Pete

 

[Stefi]

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

Regards,
Stefi

„Stevo†ezt írta:

 

[Infinitogool]

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

Regards,
Pedro J.

 

[Gary Brown]

=SUMPRODUCT(--((D1999)>=Datevalue("1/1/2008")),--((D1999)<=Datevalue("31/12/2008")),--(G1:G999="apples"))

 

[Stevo]

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

 

[Pete_UK]

The way you had it you were comparing D1999 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