Perhaps
=sumproduct(--(A:A=Date(2008,1,1)),--(A:A<=Date(2008,2,1)),(B:B="Book")+(B:B="PRI"))
?
or
=sumproduct((A:A=Date(2008,1,1))*(A:A<=Date(2008, 2,1))*((B:B="Book")+(B:B="PRI")))
?

Remember that you don't need a double unary minus if you've got an addition
anyway, such as ((B:B="Book")+(B:B="PRI"))
--
David Biddulph

"Hii Sing Chung" wrote in message
...
Thanks.

The first formula seems to be missing a bracket.
=sumproduct(--(A:A=Date(2008,1,1)+A:A<=Date(2008,2,1)),--((B:B="Book")+(B:B="PRI")))
gives #Value! error
=sumproduct(--(A:A=Date(2008,1,1))+(A:A<=Date(2008,2,1)),--((B:B="Book")+(B:B="PRI")))
gives incorrect result

The second formula works.

"Fred Smith" wrote in message
...
Because 2008-1-1 is not recognized as a date. Excel calculates it as 2008
less 1 less 1 which equals 2006. To compare dates, you need to use either
the Date or DateValue function. Also you can't compare a whole column
with an If statement. You need Sumproduct to do that.

Try:
SUMPRODUCT(--(A:A=Date(2008,1,1)+A:A<=Date(2008,2,1)),--(B:B="Book")+(B:B="PRI")))

or, more simply:
SUMPRODUCT(--(text(A:A,"yyyy-mm")="2008-01"),--(B:B="Book")+(B:B="PRI"))

Regards,
Fred

"Hii Sing Chung" wrote in message
...
Thanks.

With =IF(A:A=2008-1-1,sumproduct((B:B="BOOK")+(B:B="PRI"))), it works
fine. When I add additional condition -
=IF(and(A:A=2008-1-1,A:A<2008-2-1),SUMPRODUCT((B:B="Book")+(B:B="PRI"))),
it produces "FALSE". Why is it so?

"Eduardo" wrote in message
...
Hi,

=IF(A:A=2008-1-1,SUMPRODUCT((B:B="Book")+(B:B="PRI")))

"Hii Sing Chung" wrote:

I would like to count the number of occurrences within a date range
for
which the category belong to "BOOK" and "PRI", data examples as show
below:

Dates Categories
01-Mar-09 VCD
08-Feb-09 CDO
08-Feb-09 CDO
08-Feb-09 CDO
23-Nov-08 BOOK
08-Feb-09 BOOK
08-Feb-09 BOOK
06-Jul-08 PRI

I am using Excel 2007
Dates is the named Range for column A, and Categories the named range
for
Column B.
I had tried this formula, results in #VALUE!
=SUM(COUNTIFS(Dates,"="&DATE(2008,1,1),Categories ,"BOOK"),COUNTIFS(Dates,"="&DATE(2008,1,1),Catego ries,"PRI"))
The formula Countifs(Dates,"="&Date(2008,1,1),Categories,"BOO K") is
already
giving #VALUE! Is it that the criteria must be of the same datatype?
If I count just 1 column, it is working fine, example:
=COUNTIFS(Dates,"="&DATE(2008,1,1),Date,"<"&DATE( 2008,2,1)) works
fine, and
=SUM(COUNTIF(Categories,"BOOK"),COUNTIF(Categories ,"PRI")) works OK.

I now using the Autofilters on the 2 columns to do the counting -
subtotal(3, A1:A1). It becomes tedious when I need to do a few
combination
of date range and categories.

Thanks in advance.

Sing Chung