Counting cells in a range per multiple criteria . . .

[Dano]

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!

 

[T. Valko]

Try this:

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

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

Format as GENERAL or NUMBER

 

[Dave]

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]

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

 

[Dave]

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

 

[T. Valko]

You're welcome. Thanks for the feedback!

 

[Dano]

Fellas thanks a lot that will help me out!

Dan

You're welcome. Thanks for the feedback!

 

[Dano]

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

Fellas thanks a lot that will help me out!

Dan

 

[T. Valko]

Try this:

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

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

 

[Dano]

Alright that works perfect!
Thanks again!

Try this:

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

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

 

[T. Valko]

You're welcome!

--
Biff
Microsoft Excel MVP

Alright that works perfect!
Thanks again!