Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 11
Default 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!

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default 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!



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,388
Default 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

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default 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



  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,388
Default 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



  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default 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



  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 11
Default 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




  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default 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





  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 11
Default 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






  #10   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default 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








Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Counting cells using multiple criteria andrew.curley Excel Worksheet Functions 4 June 12th 06 04:41 PM
Counting from one range to another range, multiple criteria macamarr Excel Discussion (Misc queries) 3 June 10th 06 11:02 AM
Counting multiple cells using a criteria John Excel Discussion (Misc queries) 1 June 14th 05 04:51 PM
Counting Cells with multiple criteria.One criteria supporting wild Azhar Saleem Excel Worksheet Functions 1 January 12th 05 11:54 AM
Counting Cells with multiple criteria.One criteria supporting wild Azhar Arain Excel Worksheet Functions 1 January 12th 05 09:33 AM


All times are GMT +1. The time now is 12:16 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"