Counting cells in a range per multiple criteria . . .
 

Hello,
This is probly a simple thing to do but I cant figure it out . . .
I have a coulumn that is full of dates and I want to count how many cells
fall within a certain range of dates. For example :
A1 = 5/1/08
A2 = 5/5/08
A3 = 5/12/08
A4 = 5/14/08
A5 = 5/25/08
I want to how many cells in this range are earlier than or equal to 5/25/08
but later than or equal to 5/12/08. Should be 3 but what would the formula
look like to calculate that?

Thanks!

Counting cells in a range per multiple criteria . . .
 

Try this:

C1 = 5/12/2008
D1 = 5/25/2008

=COUNTIF(A1:A5,"="&C1)-COUNTIF(A1:A5,""&D1)

Format as GENERAL or NUMBER

--
Biff
Microsoft Excel MVP


"Dano" wrote in message
...
Hello,
This is probly a simple thing to do but I cant figure it out . . .
I have a coulumn that is full of dates and I want to count how many cells
fall within a certain range of dates. For example :
A1 = 5/1/08
A2 = 5/5/08
A3 = 5/12/08
A4 = 5/14/08
A5 = 5/25/08
I want to how many cells in this range are earlier than or equal to
5/25/08
but later than or equal to 5/12/08. Should be 3 but what would the
formula
look like to calculate that?

Thanks!

Counting cells in a range per multiple criteria . . .
 

Hi Biff,
Just jumping in...
I tried your formula - it works, of course :)
Could you please explain to me why the operators need to be in double
quotes, and why we need the & thingy?
Regards - Dave.

"T. Valko" wrote:

Try this:

C1 = 5/12/2008
D1 = 5/25/2008

=COUNTIF(A1:A5,"="&C1)-COUNTIF(A1:A5,""&D1)

Format as GENERAL or NUMBER

--
Biff
Microsoft Excel MVP

Counting cells in a range per multiple criteria . . .
 

"Why" is a hard question to answer in this case!

I don't know "why" other than to say that's how the programmers that
developed the formula parser wrote it to work.

When using a comparison operator and referring to a reference (which might
also be another function) You have to concatenate the operator to the
reference (with certain functions, COUNTIF being one of those):

=COUNTIF(A1:A5,"="&C1)
=COUNTIF(A1:A5,"="&DATE(2008,5,12))

If you tried this:

=COUNTIF(A1:A5,"=C1")

Then it evaluates "=C1" as the literal TEXT string =C1

But, if the comparison criteria is a hard coded constant you just enclose
both the operator and criteria in quotes (although concatenation will still
work):

=COUNTIF(A1:A5,"=10")
=COUNTIF(A1:A5,"="&10)

When testing for equality then no operator is required but it still works if
you do include it:

=COUNTIF(A1:A10,10)
=COUNTIF(A1:A10,"=10")
=COUNTIF(A1:A10,"="&10)

These are nuances of Excel that you learn and get used to over time!


--
Biff
Microsoft Excel MVP


"Dave" wrote in message
...
Hi Biff,
Just jumping in...
I tried your formula - it works, of course :)
Could you please explain to me why the operators need to be in double
quotes, and why we need the & thingy?
Regards - Dave.

"T. Valko" wrote:

Try this:

C1 = 5/12/2008
D1 = 5/25/2008

=COUNTIF(A1:A5,"="&C1)-COUNTIF(A1:A5,""&D1)

Format as GENERAL or NUMBER

--
Biff
Microsoft Excel MVP

Counting cells in a range per multiple criteria . . .
 

Hi Biff,
Thanks for your detailed reply. Countif is amazingly useful, and it's good
to know its "nuances"
Regards - Dave.

"T. Valko" wrote:

"Why" is a hard question to answer in this case!

I don't know "why" other than to say that's how the programmers that
developed the formula parser wrote it to work.

When using a comparison operator and referring to a reference (which might
also be another function) You have to concatenate the operator to the
reference (with certain functions, COUNTIF being one of those):

=COUNTIF(A1:A5,"="&C1)
=COUNTIF(A1:A5,"="&DATE(2008,5,12))

If you tried this:

=COUNTIF(A1:A5,"=C1")

Then it evaluates "=C1" as the literal TEXT string =C1

But, if the comparison criteria is a hard coded constant you just enclose
both the operator and criteria in quotes (although concatenation will still
work):

=COUNTIF(A1:A5,"=10")
=COUNTIF(A1:A5,"="&10)

When testing for equality then no operator is required but it still works if
you do include it:

=COUNTIF(A1:A10,10)
=COUNTIF(A1:A10,"=10")
=COUNTIF(A1:A10,"="&10)

These are nuances of Excel that you learn and get used to over time!


--
Biff
Microsoft Excel MVP

Counting cells in a range per multiple criteria . . .
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Dave" wrote in message
...
Hi Biff,
Thanks for your detailed reply. Countif is amazingly useful, and it's good
to know its "nuances"
Regards - Dave.

"T. Valko" wrote:

"Why" is a hard question to answer in this case!

I don't know "why" other than to say that's how the programmers that
developed the formula parser wrote it to work.

When using a comparison operator and referring to a reference (which
might
also be another function) You have to concatenate the operator to the
reference (with certain functions, COUNTIF being one of those):

=COUNTIF(A1:A5,"="&C1)
=COUNTIF(A1:A5,"="&DATE(2008,5,12))

If you tried this:

=COUNTIF(A1:A5,"=C1")

Then it evaluates "=C1" as the literal TEXT string =C1

But, if the comparison criteria is a hard coded constant you just enclose
both the operator and criteria in quotes (although concatenation will
still
work):

=COUNTIF(A1:A5,"=10")
=COUNTIF(A1:A5,"="&10)

When testing for equality then no operator is required but it still works
if
you do include it:

=COUNTIF(A1:A10,10)
=COUNTIF(A1:A10,"=10")
=COUNTIF(A1:A10,"="&10)

These are nuances of Excel that you learn and get used to over time!


--
Biff
Microsoft Excel MVP

Counting cells in a range per multiple criteria . . .
 

Fellas thanks a lot that will help me out!

Dan

"T. Valko" wrote:

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Dave" wrote in message
...
Hi Biff,
Thanks for your detailed reply. Countif is amazingly useful, and it's good
to know its "nuances"
Regards - Dave.

"T. Valko" wrote:

"Why" is a hard question to answer in this case!

I don't know "why" other than to say that's how the programmers that
developed the formula parser wrote it to work.

When using a comparison operator and referring to a reference (which
might
also be another function) You have to concatenate the operator to the
reference (with certain functions, COUNTIF being one of those):

=COUNTIF(A1:A5,"="&C1)
=COUNTIF(A1:A5,"="&DATE(2008,5,12))

If you tried this:

=COUNTIF(A1:A5,"=C1")

Then it evaluates "=C1" as the literal TEXT string =C1

But, if the comparison criteria is a hard coded constant you just enclose
both the operator and criteria in quotes (although concatenation will
still
work):

=COUNTIF(A1:A5,"=10")
=COUNTIF(A1:A5,"="&10)

When testing for equality then no operator is required but it still works
if
you do include it:

=COUNTIF(A1:A10,10)
=COUNTIF(A1:A10,"=10")
=COUNTIF(A1:A10,"="&10)

These are nuances of Excel that you learn and get used to over time!


--
Biff
Microsoft Excel MVP

Counting cells in a range per multiple criteria . . .
 

Try this:

G1 = 5/12/2008
H1 = 5/25/2008
I1 = Done

=SUMPRODUCT(--(A1:A5=G1),--(A1:A5<=H1),--(E1:E5=I1))

--
Biff
Microsoft Excel MVP


"Dano" wrote in message
...
Ok I have another question with this same thing here . . .
Say the dates in A1 thru A5 remain the same. I add the following column
:
E1 = "Done"
E2 = "Open"
E3 = "Open"
E4 = "Done"
E5 = "Done"

and I want to know how many cells in this range are earlier than or equal
to
5/25/08 but later than or equal to 5/12/08 and only count the cells that
have
"Done" in column E? Should be 2 but how would you constuct a formula for
that?

Thanks a lot for your help.

Dan



"Dano" wrote:

Fellas thanks a lot that will help me out!

Dan

"T. Valko" wrote:

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Dave" wrote in message
...
Hi Biff,
Thanks for your detailed reply. Countif is amazingly useful, and it's
good
to know its "nuances"
Regards - Dave.

"T. Valko" wrote:

"Why" is a hard question to answer in this case!

I don't know "why" other than to say that's how the programmers that
developed the formula parser wrote it to work.

When using a comparison operator and referring to a reference (which
might
also be another function) You have to concatenate the operator to
the
reference (with certain functions, COUNTIF being one of those):

=COUNTIF(A1:A5,"="&C1)
=COUNTIF(A1:A5,"="&DATE(2008,5,12))

If you tried this:

=COUNTIF(A1:A5,"=C1")

Then it evaluates "=C1" as the literal TEXT string =C1

But, if the comparison criteria is a hard coded constant you just
enclose
both the operator and criteria in quotes (although concatenation
will
still
work):

=COUNTIF(A1:A5,"=10")
=COUNTIF(A1:A5,"="&10)

When testing for equality then no operator is required but it still
works
if
you do include it:

=COUNTIF(A1:A10,10)
=COUNTIF(A1:A10,"=10")
=COUNTIF(A1:A10,"="&10)

These are nuances of Excel that you learn and get used to over time!


--
Biff
Microsoft Excel MVP

Counting cells in a range per multiple criteria . . .
 

Alright that works perfect!
Thanks again!

"T. Valko" wrote:

Try this:

G1 = 5/12/2008
H1 = 5/25/2008
I1 = Done

=SUMPRODUCT(--(A1:A5=G1),--(A1:A5<=H1),--(E1:E5=I1))

--
Biff
Microsoft Excel MVP


"Dano" wrote in message
...
Ok I have another question with this same thing here . . .
Say the dates in A1 thru A5 remain the same. I add the following column
:
E1 = "Done"
E2 = "Open"
E3 = "Open"
E4 = "Done"
E5 = "Done"

and I want to know how many cells in this range are earlier than or equal
to
5/25/08 but later than or equal to 5/12/08 and only count the cells that
have
"Done" in column E? Should be 2 but how would you constuct a formula for
that?

Thanks a lot for your help.

Dan



"Dano" wrote:

Fellas thanks a lot that will help me out!

Dan

"T. Valko" wrote:

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Dave" wrote in message
...
Hi Biff,
Thanks for your detailed reply. Countif is amazingly useful, and it's
good
to know its "nuances"
Regards - Dave.

"T. Valko" wrote:

"Why" is a hard question to answer in this case!

I don't know "why" other than to say that's how the programmers that
developed the formula parser wrote it to work.

When using a comparison operator and referring to a reference (which
might
also be another function) You have to concatenate the operator to
the
reference (with certain functions, COUNTIF being one of those):

=COUNTIF(A1:A5,"="&C1)
=COUNTIF(A1:A5,"="&DATE(2008,5,12))

If you tried this:

=COUNTIF(A1:A5,"=C1")

Then it evaluates "=C1" as the literal TEXT string =C1

But, if the comparison criteria is a hard coded constant you just
enclose
both the operator and criteria in quotes (although concatenation
will
still
work):

=COUNTIF(A1:A5,"=10")
=COUNTIF(A1:A5,"="&10)

When testing for equality then no operator is required but it still
works
if
you do include it:

=COUNTIF(A1:A10,10)
=COUNTIF(A1:A10,"=10")
=COUNTIF(A1:A10,"="&10)

These are nuances of Excel that you learn and get used to over time!


--
Biff
Microsoft Excel MVP

Counting cells in a range per multiple criteria . . .
 

You're welcome!

--
Biff
Microsoft Excel MVP


"Dano" wrote in message
...
Alright that works perfect!
Thanks again!

"T. Valko" wrote:

Try this:

G1 = 5/12/2008
H1 = 5/25/2008
I1 = Done

=SUMPRODUCT(--(A1:A5=G1),--(A1:A5<=H1),--(E1:E5=I1))

--
Biff
Microsoft Excel MVP


"Dano" wrote in message
...
Ok I have another question with this same thing here . . .
Say the dates in A1 thru A5 remain the same. I add the following
column
:
E1 = "Done"
E2 = "Open"
E3 = "Open"
E4 = "Done"
E5 = "Done"

and I want to know how many cells in this range are earlier than or
equal
to
5/25/08 but later than or equal to 5/12/08 and only count the cells
that
have
"Done" in column E? Should be 2 but how would you constuct a formula
for
that?

Thanks a lot for your help.

Dan



"Dano" wrote:

Fellas thanks a lot that will help me out!

Dan

"T. Valko" wrote:

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Dave" wrote in message
...
Hi Biff,
Thanks for your detailed reply. Countif is amazingly useful, and
it's
good
to know its "nuances"
Regards - Dave.

"T. Valko" wrote:

"Why" is a hard question to answer in this case!

I don't know "why" other than to say that's how the programmers
that
developed the formula parser wrote it to work.

When using a comparison operator and referring to a reference
(which
might
also be another function) You have to concatenate the operator to
the
reference (with certain functions, COUNTIF being one of those):

=COUNTIF(A1:A5,"="&C1)
=COUNTIF(A1:A5,"="&DATE(2008,5,12))

If you tried this:

=COUNTIF(A1:A5,"=C1")

Then it evaluates "=C1" as the literal TEXT string =C1

But, if the comparison criteria is a hard coded constant you just
enclose
both the operator and criteria in quotes (although concatenation
will
still
work):

=COUNTIF(A1:A5,"=10")
=COUNTIF(A1:A5,"="&10)

When testing for equality then no operator is required but it
still
works
if
you do include it:

=COUNTIF(A1:A10,10)
=COUNTIF(A1:A10,"=10")
=COUNTIF(A1:A10,"="&10)

These are nuances of Excel that you learn and get used to over
time!


--
Biff
Microsoft Excel MVP