Counting number of rows between date range and meeting 2 string cr
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
|