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