Count occurences between dates
 

Hello! I have a spreadsheet where I want to count the occurences of a
type between two given dates.

My spreadsheet is as follows:

Column *A* contains the date of the transaction
Column *E* contains the type of the transaction
Column *F* contains the result of the transaction - the three valid
entries are "Yes", "No", or can be left blank (if the field is left
blank, "Yes" is assumed)

An example spreadsheet would be:


05/12/2004 Standard Yes
06/12/2004 Standard Yes
06/12/2004 Standard No
06/12/2004 Standard Yes
07/12/2004 Standard <BLANK
07/12/2004 Advanced Yes
14/12/2004 Standard Yes
15/12/2004 Standard No

I would like to:

*a)* count the number of Standard (regardless of yes, no or blank)
between 6th and 12th December (answer should be 4)

and

*b)* count the number of Standard and "no" between 6th and 12th
December (answer should be 1)

Please help, this has been driving me nuts!


--
DJ Dusty
------------------------------------------------------------------------
DJ Dusty's Profile: http://www.excelforum.com/member.php...o&userid=16335
View this thread: http://www.excelforum.com/showthread...hreadid=277230

Hi DJ

formula for Q1
=SUMPRODUCT((A1:A8=DATE(2004,12,6))*(B1:B8="Stand ard"))-SUMPRODUCT((A1:A8=DATE(2004,12,12))*(B1:B8="Stand ard"))

formula for Q2
=SUMPRODUCT((A1:A8=DATE(2004,12,6))*(B1:B8="Stand ard")*(C1:C8="No"))-
SUMPRODUCT((A1:A8=DATE(2004,12,12))*(B1:B8="Stand ard")*(C1:C8="No"))

for details on how the SUMPRODUCT function works check out
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Cheers
JulieD

"DJ Dusty" wrote in message
...

Hello! I have a spreadsheet where I want to count the occurences of a
type between two given dates.

My spreadsheet is as follows:

Column *A* contains the date of the transaction
Column *E* contains the type of the transaction
Column *F* contains the result of the transaction - the three valid
entries are "Yes", "No", or can be left blank (if the field is left
blank, "Yes" is assumed)

An example spreadsheet would be:


05/12/2004 Standard Yes
06/12/2004 Standard Yes
06/12/2004 Standard No
06/12/2004 Standard Yes
07/12/2004 Standard <BLANK
07/12/2004 Advanced Yes
14/12/2004 Standard Yes
15/12/2004 Standard No

I would like to:

*a)* count the number of Standard (regardless of yes, no or blank)
between 6th and 12th December (answer should be 4)

and

*b)* count the number of Standard and "no" between 6th and 12th
December (answer should be 1)

Please help, this has been driving me nuts!


--
DJ Dusty
------------------------------------------------------------------------
DJ Dusty's Profile:
http://www.excelforum.com/member.php...o&userid=16335
View this thread: http://www.excelforum.com/showthread...hreadid=277230

a)
=SUMPRODUCT(--(E1:E10="Standard"),--(A1:A10=--("2004/12/06")),--(A1:A10<=--
("2004/12/12")),--(F1:F10="No"))

b)
=SUMPRODUCT(--(E1:E10="Standard"),--(A1:A10=--("2004/12/06")),--(A1:A10<=--
("2004/12/12")),--(F1:F10="No"))

Hopefully you get the kidea.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"DJ Dusty" wrote in message
...

Hello! I have a spreadsheet where I want to count the occurences of a
type between two given dates.

My spreadsheet is as follows:

Column *A* contains the date of the transaction
Column *E* contains the type of the transaction
Column *F* contains the result of the transaction - the three valid
entries are "Yes", "No", or can be left blank (if the field is left
blank, "Yes" is assumed)

An example spreadsheet would be:


05/12/2004 Standard Yes
06/12/2004 Standard Yes
06/12/2004 Standard No
06/12/2004 Standard Yes
07/12/2004 Standard <BLANK
07/12/2004 Advanced Yes
14/12/2004 Standard Yes
15/12/2004 Standard No

I would like to:

*a)* count the number of Standard (regardless of yes, no or blank)
between 6th and 12th December (answer should be 4)

and

*b)* count the number of Standard and "no" between 6th and 12th
December (answer should be 1)

Please help, this has been driving me nuts!


--
DJ Dusty
------------------------------------------------------------------------
DJ Dusty's Profile:
http://www.excelforum.com/member.php...o&userid=16335
View this thread: http://www.excelforum.com/showthread...hreadid=277230

1)SUMPRODUCT((VALUE(A1:A8)=VALUE("12/6/2004"))*(VALUE(A1:A8)<=VALUE("12/12/2004"))*(B1:B8="Standard"))

2)SUMPRODUCT((VALUE(A1:A8)=VALUE("12/6/2004"))*(VALUE(A1:A8)<=VALUE("12/12/2004"))*(B1:B8="Standard")*(C1:C8="No"))


--
anilsolipuram
------------------------------------------------------------------------
anilsolipuram's Profile: http://www.excelforum.com/member.php...o&userid=16271
View this thread: http://www.excelforum.com/showthread...hreadid=277230