Hi!
OK, change the argument order in the DATE functions:
2005,1,1
2005,1,31
The DATE function arguments are, in order:
=DATE(year, month, day)
Biff
-----Original Message-----
I'm using the first formula...
I figured out the error problem but now I'm get a value
of 0. I have the
dates set as dates and in the matching date formats.
Here is the formula I
have entered:
=SUMPRODUCT(--(A2:A13="Theft from
Auto"),--(B2:B13=DATE(1,1,2005)),--(B2:B13<=DATE
(1,31,2005)))
Column A holds the types of crime and column B holds the
date.
I sure do appreciate your helping me with this...
"Biff" wrote:
Hi!
Which formula did you use?
#NUM! is an error code that means a numeric value in
the
formula is incorrect.
Are your dates really dates and not text? But even if
that
were the case you should just get a return value of 0.
If you used the second formula with the EOMONTH
function
and didn't have the ATP installed you would get a
#NAME?
error.
If the ranges aren't exactly the same size then you
would
get a #VALUE! error.
I can't see why you would get #NUM!.
Post back the exact formula you used.
Biff
-----Original Message-----
I entered in the code but the text "#NUM!" is now
appearing where the value
should be. Am I missing something?
By the way, you're not on the list =)
"Biff" wrote:
Hi!
I sure hope I'm not in that list!
Here's some formula options:
This will count all thefts that occured in Jan 05.
=SUMPRODUCT(--(A1:A11="theft"),--(B1:B11=DATE
(2005,1,1)),-
-(B1:B11<=DATE(2005,1,31)))
To make it more user friendly:
You could put a dropdown that lists all the crime
catagories in say cell C1. You can enter a date in
cell
D1
such as 1/1 (which will default to the current year)
and
use a formula like this:
=SUMPRODUCT(--(A1:A11=C1),--(B1:B11=D1),--
(B1:B11<=EOMONTH
(D1,0)))
The EOMONTH function requires the Analysis ToolPak
add-
in
be installed.
Also, when entering the date always use the first
day
of
the month: 1/1, 5/1, 12/1 (I have my date format as
mm/dd/yy)
Biff
-----Original Message-----
I use Excel to maintain crime statistics. I would
like
to use the COUNTIF
function (or whatever works) to count a crime by
month.
I have the crimes in
column A and the date in column B. Basically what
I
am
trying to do for
example is count all of the thefts in January. I
know
how to count all
thefts, but that will give me all of the thefts for
that
year, not just the
ones that happened in January and in the same I
know
how
to count all the
crimes in January, but not just the thefts that
occured
in January. Any
thoughts?
.
.
.
|