Counting Cells with Multiple Range Criteria (Excel 2003)

[Jennifer]

I'm trying to count the number of rows that matches multiple criteria in
multiple ranges. As an example, I want to count the number of rows where
both Column A and Column B have a date between 01-01-05 and 12-31-05. Where:

Column A Column B
01-02-05 12-20-05
01-06-05 02-20-06
05-06-09 03-07-05
01-02-06 01-09-06

So, in this instance, I want it to count only row 1 because both columns
match the criteria.

I've tried a couple of different Countif statements, but I can never get it
to count using the multiple criteria and ranges. Please assist.

Things I've tried:
=Countif(and(A:A,B:B), ">"&C1))+countif(and(A:A,B:B), "<"&D1) - - where C1
is equal to 01-01-05 and D1 is 12-31-05

 

[Luke M]

=SUMPRODUCT(--(A2:A100>C1),--(A2:A100<D1),--(B2:B100>C1),--(B2:B100<D1))

Note that unless you are using XL 2007, you can't callout the entire column
within SUMPRODUCT.

 

[Jennifer]

Luke - this is returning an error when I enter it in exactly like you have
it. When I change the "--" to "-", I get a returned value of 1, so it's
still not pulling it correctly.

 

[Max]

.. I want to count the number of rows where both Column A

and Column B have a date between 01-01-05 and 12-31-05

An alternative to try, presuming data in cols A and B are all real dates:
=SUMPRODUCT((TEXT(A1:A4,"yyyy")="2005")*(TEXT(B1:B4,"yyyy")="2005"))
Adapt the ranges to suit the actual extents. Any joy? hit the YES below

 

[Jennifer]

That will only work for that particular scenario...I also need it to work for
date ranges between 02-01-05 and 01-31-06, so I can't limit it to just the
year.

 

[Max]

.. I also need it to work for date ranges between 02-01-05 and 01-31-06,

so I can't limit it to just the year

Try it like this then, with explicit, unambiguous date ranges defined:
=SUMPRODUCT((A1:A4>=--"2 Jan 2005")*(A1:A4<=--"31 Jan 2006")*(B1:B4>=--"2
Jan 2005")*(B1:B4<=--"31 Jan 2006"))
Success? hit the YES below

 

[Luke M]

What error is it spitting out? To explain, the "--" is there to convert the
results from the comparison (true/false) into numbers. The first "-" converts
it to (-1/0) and the second "-" converts it to (1/0). The 4 arrays are then
multipied across each other, and only when four 1's are found is an entry
counted.

Playing around with the formula as written, I am unable to get it to create
an error (even when text is used in some of the fields). Possible debugging
method would be to change the arrays that are being examined until you find
the area that is causing trouble?

Of final note, the array "sizes" must be identical. For instance, in the
formula I gave all arrays are 99 rows. Sorry this isn't more helpful.

 

[David Biddulph]

What error is it reporting?
The formula is fine, so it sounds as if you tried to retype the formula and
got it wrong. Rather than retyping, copy it from the newsgroup and paste
into the formula bar on your spreadsheet.