Counting Cells with certain date ranges as values

G

Guest

I need to count cells withdates in theme in a column. So that would be a
CountA function; but only if the values in the cells are within a certain
date range, a COUNTIF function. Here's what I thought:
=COUNTIF('All Employees'!O1351:O1364,">12/31/05,<2/1/06")
It returns a zero, which I know is not correct, as I checked it on a smaller
sample.

What am I doing wrong?

I also tried a SumIF function, but because Excel works on the premise that
all dates are really number of days since the turn of the century (1900), it
returns a ridiculous number.
 
G

Guest

This one seems to work:
=((COUNTIF('All Employees'!O1351:BC1364,">12/31/05"))-(COUNTIF('All
Employees'!O1361:BC1364,">2/1/06")))

Think this is OK?
 
P

Peo Sjoblom

Try

=COUNTIF('All Employees'!O1351:O1364,">"&DATE(2005,12,31))-COUNTIF('All
Employees'!O1351:O1364,">="&DATE(2006,2,1))

or


=SUMPRODUCT(--('All Employees'!O1351:O1364>--"2005-12-31"),--('All
Employees'!O1351:O1364<--"2006-02-01"))


--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com
 
G

Guest

This one returns a #VALUE! error:
=COUNTIF('All Employees'!O1351:O1364,">"&DATE(2005,12,31))-COUNTIF('All
Employees'!O1351:O1364,">="&DATE(2006,2,1))

This one retuens a #REF! error:
=SUMPRODUCT(--('All Employees'!O1351:O1364>--"2005-12-31"),--('All
Employees'!O1351:O1364<--"2006-02-01"))

Any other ideas? Please?
 
P

Peo Sjoblom

Then your dates are not Excel dates but text so you can't really use a less
than condition
Btw, the second formula should not return a ref error unless copied and
pasted incorrectly, it should return zero since the dates are not numerical.
To use the formulas you must use "real" dates that Excel understand as
numbers in O1351:O1364


--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com
 
G

Guest

What do you mean real dates? I am entering them as 12/1/05 and having the
cell display as Dec-06 for conciseness. Is this wrong?
 
P

Peo Sjoblom

Are you sure they are dates, compare them like =O1364-O1351
do you get a number as result or do you get the value error, if you get a
number they are real excel dates, if you get the error they are text and
cannot be used in calculations. If you don't have any leading or trailing
spaces you can convert them by copying an empty cell, select the dates and
do edit paste special and select add. However since you say you enter them
like 12/1/05 and use formatting to display as Dec-05 (I assume you had a
typo since 12/1/05 should not be Dec-06) then they are dates

Try

=COUNTIF('All Employees'!O1351:O1364,">"&E1)-COUNTIF('All
Employees'!O1351:O1364,">="&F1)

and put the dates 12/31/05 in E1 and 02/01/06 in F1, make sure they are
dates (widen the column and see that they are right aligned without any
formatting applied) if you get zero it's text, if you get an error
then there's an error in the range itself or the formula has been copied and
pasted incorrectly


--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com
 
G

Guest

Okay. Here's what I got. I entered:
=COUNTIF('All Employees'' Training'!O1352:O1364,">"&12/31/5)-COUNTIF('All
Employees'' Training'!O1352:O1364,">="&2/1/6)

There was a ref. error.I fixed that. And, it's dropping the zero before the
year?!

It returned a zero. So that means my dates are not dates. IN the cell it
reads Dec-05 (yes, that was a typo) and the formula bar reads 12/1/2005.

2005! Four digits!!!! Do you think that is the problem? I'll try that:
=COUNTIF('All Employees'' Training'!O1352:O1364,">"&12/31/2005)-COUNTIF('All
Employees'' Training'!O1352:O1364,">="&2/1/2006)

Still returns a zero. I know that is not correct, because this sample
(Training'!O1352:O1364) has two entries within that range.

I'm getting discouraged here...but I really appreciate your help. Any
thoughts?

You said if it returns a zero, they are text. How do I get them to be dates?
 
D

Dave Peterson

If you use 12/31/5, excel will see that as 12 divided by 31 divided by 5 (.077
about).

That's why Peo suggested that you either put the date in another cell or use
this:

">"&DATE(2005,12,31)
 
G

Guest

WAHOOO!!! It returned a 2. The correct answer! It worked! Once I understood
what you guys were saying to do. Thanks!
But now it has a new issue: I decided to test it by adding in some fake
entries (2/1/06, 2/2/06, and 1/2/06), so the answer should now be 3; it is
returning a 5. It's counting the other two Feb entries too?!

Here's what I have:
=COUNTIF('All Employees''
Training'!O1352:O1364,">"&DATE(2005,12,31)-COUNTIF('All Employees''
Training'!O1352:O1364,"<"&DATE(2006,2,1)))
 
P

Peo Sjoblom

=COUNTIF('All Employees''
Training'!O1352:O1364,">"&DATE(2005,12,31)-COUNTIF('All Employees''
Training'!O1352:O1364,">="&DATE(2006,2,1)))

if you want to count the dates for Jan 06 use the above AND if you only want
to check on a monthly basis you can use the below as well

=SUMPRODUCT(--(MONTH(Range)=1),--(YEAR(Range)=2006))


replace Range with the sheet name and the cell range


--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com
 
G

Guest

Yes, that is exactly what I am trying to do: check monthly & weekly.

Is there someway to check weekly? Or will I have to use:
=COUNTIF('All
Employees''Training'!O1352:O1364,">"&DATE(2005,12,31)-COUNTIF('All
Employees''Training'!O1352:O1364,">="&DATE(2006,2,1)))
and input the date ranges?

Scenaro: I am looking at training records and determining how many have
taken each course each month, and how many trainings per week, as well as who
is due for yearly training. The higher ups are asking me to justify my job.
If you can help with that yearly one, I would be eternally greatful. I was
thinking something like:
=COUNTIF('All Employees''Training'!O1352:O1364,"<"&DATE((TODAY())-365)
Think it will work? I haven't tested it.

You guys have been so very helpful. Sincerest thanks.
 
P

Peo Sjoblom

You might want to try a pivot table as well, that would easily get all
months done, weeks are also possible using the formula you have although I
would use the alternative where you replace the DATE() formulas with cells
where you put the dates in question


--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com
 

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

Counting Values 3
Creating Function to Reference Cells that Contain Certain Values 5
how to do a countif - want cells that are before or on a certain dateand do not want to hard code th 1
Adding dates in columns 2
COUNTIF for date and amount 1
Counting fileterd cells with spesific value 4
counting cells containing certain ranges of dates 1
Counting cells with specific date ranges 1

Top