CountIf - Counting cells between a range of numbers

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am sure I'm missing something obvious, but I don't know what. I am interested in using the countif function to count the number of cells that fall between a certain time frame (i.e. 12/1/03 and 12/31/03) I can not figure out how to write a formula for "between". Any suggestions?

One more thought. Is it possible to write a formula as mentioned above, that would begin to recalculate at the start of a new month?

Thanks for the help!

matt
 
=COUNTIF(B1:B1100,">=5")-COUNTIF(B1:B1100,">=26")

--
Greeting from the Gulf Coast!
http://myweb.cableone.net/twodays
Matt said:
I am sure I'm missing something obvious, but I don't know what. I am
Click to expand...
interested in using the countif function to count the number of cells that
fall between a certain time frame (i.e. 12/1/03 and 12/31/03) I can not
figure out how to write a formula for "between". Any suggestions?
One more thought. Is it possible to write a formula as mentioned above,
Click to expand...
that would begin to recalculate at the start of a new month?
 
Thank you. I'll be trying it first thing tomorrow. I'm not sure I understand how it works so I might have another question tomorrow. Thanks again for your assistance.

Matt
 
Suppose the dates are in col A, A1 downwards

Put in say, C1: 12/1/03 (the start date)
Put in say, D1: 12/31/03 (the end date)

Put in B1: =COUNTIF(A:A,">="&C1)-COUNTIF(A:A,">"&D1)

Format B1 as number
(via Format > Cells > Number tab
Number (zero decimal place) > OK)
Click to expand...

B1 will return the count of the number of days in col A
which fall in-between the start and end dates (inclusive)
specified in C1 and D1

And if you have other pairs of start and end dates
specified in C2 & D2 downwards,
i.e. in C2 & D2, C3 & D3, etc

just extend by copying the formula in B1 down col B
to derive the corresponding counts

--
hth
Max
-----------------------------------------
Please reply in thread
Use xdemechanik <at>yahoo<dot>com for email
--------------------------------------------------------
Matt said:
I am sure I'm missing something obvious, but I don't know what. I am
Click to expand...
interested in using the countif function to count the number of cells that
fall between a certain time frame (i.e. 12/1/03 and 12/31/03) I can not
figure out how to write a formula for "between". Any suggestions?
One more thought. Is it possible to write a formula as mentioned above,
Click to expand...
that would begin to recalculate at the start of a new month?
 
Slight correction ("days" should read "dates") in:
B1 will return the count of the number of days in col A
which fall in-between the start and end dates (inclusive)
specified in C1 and D1
Click to expand...

Should read as
 
where cells d1 and d2 have your dates
=sumproduct((a2:a200>d1)*(a2:a200<=d2))

--
Don Guillett
SalesAid Software
(e-mail address removed)
Matt said:
I am sure I'm missing something obvious, but I don't know what. I am
Click to expand...
interested in using the countif function to count the number of cells that
fall between a certain time frame (i.e. 12/1/03 and 12/31/03) I can not
figure out how to write a formula for "between". Any suggestions?
One more thought. Is it possible to write a formula as mentioned above,
Click to expand...
that would begin to recalculate at the start of a new month?
 
Thank you guys, your information was very thorough. If I may, there is a twist to consider. Column B has the Start Date.
I want to tally all of the dates in Column B that are greater than 12/10/3 and less than 12/31/03. This may be a stupid question but can I put in these fixed values instead of a cell reference?

Last piece of the pie. Is there a way within regular Excel to set up a "Case" statement. Thing is, I want to count the number of instances of a date that falls between the first and last date of the month, EVERY MONTH. So I could set up a column that will have January - December listed in it and a "Case" statement that would look at the start date and add it to the CountIf statement for the appropriate month. OR - Do I have to write the formula in each cell that I am looking for this data in?

Thanks for the help, again!

matt
 
I do not know if you have had a look at Data>Filter>Autofilter; that will do
what you want, I think. Otherwise, there are several worksheet functions to
break down data by months, such as:
=MONTH(A2), which will return 1 for January, 2 for February, etc. for the
date in A2.
Another:
=COUNTIF(A2:A1300,">=1/1/03")-COUNTIF(A1:A13,">1/31/03")
--
Greeting from the Gulf Coast!
http://myweb.cableone.net/twodays
Matt said:
Thank you guys, your information was very thorough. If I may, there is a
Click to expand...
twist to consider. Column B has the Start Date.
I want to tally all of the dates in Column B that are greater than 12/10/3
Click to expand...
and less than 12/31/03. This may be a stupid question but can I put in
these fixed values instead of a cell reference?
Last piece of the pie. Is there a way within regular Excel to set up a
Click to expand...
"Case" statement. Thing is, I want to count the number of instances of a
date that falls between the first and last date of the month, EVERY MONTH.
So I could set up a column that will have January - December listed in it
and a "Case" statement that would look at the start date and add it to the
CountIf statement for the appropriate month. OR - Do I have to write the
formula in each cell that I am looking for this data in?
 
Back
Top