Counting number of rows between date range and meeting 2 string criteria

[Hii Sing Chung]

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),Categories,"PRI"))
The formula Countifs(Dates,">="&Date(2008,1,1),Categories,"BOOK") 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

 

[Luke M]

There's prb a way to do this using COUNTIFS, but as I'm personally more
familiar w/ SUMPRODUCT, you could do:

=SUMPRODUCT(--(Dates>=DATE(2008,1,1)),--(Dates<=DATE(2008,2,1)),--((Categories="BOOK")+(Categories="PRI")))

Note that for the second half where you have multiple possible criteria, you
are adding the arrays together (in case you want to add other criteria).

 

[Eduardo]

Hi,

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

 

[Hii Sing Chung]

Thanks.

I tried your formula, it turned out #VALUE! as well. So, I feel the problem
can't be with the formula. I then use the A5:A500 in place of Dates and
B5:B5000 in place of Categories, then it works. A check in the named range,
I found that Dates is A5:A500 and Categories is B4:B5000, where B4 is the
header. Is it that the two columns must have the same number of row?

 

[Hii Sing Chung]

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?

 

[Fred Smith]

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

 

[Eduardo]

Thanks for the feedback, have a great day

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?

 

[Luke M]

Yes, both arrays/ranges must have same amount of rows.

 

[Hii Sing Chung]

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.

 

[David Biddulph]

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

 

[Fred Smith]

You're welcome. Thanks for the feedback.

Regards,
Fred.

 

[Hii Sing Chung]

Thanks.

Both formulae work. The only correction is the second criteria should be
A:A<Date(2008,2,1).