Count occurences between dates

[DJ Dusty]

Hello! I have a spreadsheet where I want to count the occurences of
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 vali
entries are "Yes", "No", or can be left blank (if the field is lef
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 12t
December (answer should be 1)

Please help, this has been driving me nuts

 

[JulieD]

Hi DJ

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

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

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

Cheers
JulieD

 

[Bob Phillips]

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)

 

[anilsolipuram]

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