Counting cells in a range per multiple criteria . . .

  • Thread starter Thread starter Dano
  • Start date Start date
D

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!
 
Try this:

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

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

Format as GENERAL or NUMBER
 
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.
 
"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!
 
Hi Biff,
Thanks for your detailed reply. Countif is amazingly useful, and it's good
to know its "nuances"
Regards - Dave.
 
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 said:
Fellas thanks a lot that will help me out!

Dan
Click to expand...
 
Try this:

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

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

count on 3 criteria 3
Count data in adjacent cell if criteria met 0
Sum with multiple criteria 6
Only adding nonzeroes 2
COUNT function 2
Linux turns 29: The biggest events in its history so far 5
Count the number of dates less than a given date 1
counting multiple dates in a range 1

Back
Top