Counting values based on multiple conditions
 

Hello,

I am trying to count the number of times the word "homicide" occurs in cells
J2:J1500 between the dates of 10-1-2008 and 10-31-2008...the dates are listed
in cells A2:A1500...I am fairly fluent in excel and I tried this
formula...=SUM(IF((J2:J1500="Homicide",IF(A2:A1500 =DATEVALUE("10/1/2008"))*(A2:A1500<=DATEVALUE("10/31/2008)),1,0))
.....I've also tried a couple other ways to formulate it but i keep getting
an error..any help would be appreciated..also worth noting is this is for my
security department..i can see how some might think searching for the word
homicide would be odd..haha

Counting values based on multiple conditions
 

=SUMPRODUCT(--(J2:J1500="Homicide"), --(A2:A1500=DATEVALUE("10/1/2008")),
--(A2:A1500<=DATEVALUE("10/31/2008)) )
For more details on SUMPRODUCT
Bob Phillips
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
J.E McGimpsey
http://mcgimpsey.com/excel/formulae/doubleneg.html
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Paul" wrote in message
...
Hello,

I am trying to count the number of times the word "homicide" occurs in
cells
J2:J1500 between the dates of 10-1-2008 and 10-31-2008...the dates are
listed
in cells A2:A1500...I am fairly fluent in excel and I tried this
formula...=SUM(IF((J2:J1500="Homicide",IF(A2:A1500 =DATEVALUE("10/1/2008"))*(A2:A1500<=DATEVALUE("10/31/2008)),1,0))
.....I've also tried a couple other ways to formulate it but i keep
getting
an error..any help would be appreciated..also worth noting is this is for
my
security department..i can see how some might think searching for the word
homicide would be odd..haha

Counting values based on multiple conditions
 

Hi,

Try this

=SUMPRODUCT((J2:J25="Homicide")*(A2:A25=DATE(2008 ,10,1))*(A2:A25<=DATE(2008,10,31)))

all one line


Mike

"Paul" wrote:

Hello,

I am trying to count the number of times the word "homicide" occurs in cells
J2:J1500 between the dates of 10-1-2008 and 10-31-2008...the dates are listed
in cells A2:A1500...I am fairly fluent in excel and I tried this
formula...=SUM(IF((J2:J1500="Homicide",IF(A2:A1500 =DATEVALUE("10/1/2008"))*(A2:A1500<=DATEVALUE("10/31/2008)),1,0))
.....I've also tried a couple other ways to formulate it but i keep getting
an error..any help would be appreciated..also worth noting is this is for my
security department..i can see how some might think searching for the word
homicide would be odd..haha

Counting values based on multiple conditions
 

worked perfectly...happy holidays

paul

"Mike H" wrote:

Hi,

Try this

=SUMPRODUCT((J2:J25="Homicide")*(A2:A25=DATE(2008 ,10,1))*(A2:A25<=DATE(2008,10,31)))

all one line


Mike

"Paul" wrote:

Hello,

I am trying to count the number of times the word "homicide" occurs in cells
J2:J1500 between the dates of 10-1-2008 and 10-31-2008...the dates are listed
in cells A2:A1500...I am fairly fluent in excel and I tried this
formula...=SUM(IF((J2:J1500="Homicide",IF(A2:A1500 =DATEVALUE("10/1/2008"))*(A2:A1500<=DATEVALUE("10/31/2008)),1,0))
.....I've also tried a couple other ways to formulate it but i keep getting
an error..any help would be appreciated..also worth noting is this is for my
security department..i can see how some might think searching for the word
homicide would be odd..haha

Counting values based on multiple conditions
 

Your welcome, glad I could help

"Paul" wrote:

worked perfectly...happy holidays

paul

"Mike H" wrote:

Hi,

Try this

=SUMPRODUCT((J2:J25="Homicide")*(A2:A25=DATE(2008 ,10,1))*(A2:A25<=DATE(2008,10,31)))

all one line


Mike

"Paul" wrote:

Hello,

I am trying to count the number of times the word "homicide" occurs in cells
J2:J1500 between the dates of 10-1-2008 and 10-31-2008...the dates are listed
in cells A2:A1500...I am fairly fluent in excel and I tried this
formula...=SUM(IF((J2:J1500="Homicide",IF(A2:A1500 =DATEVALUE("10/1/2008"))*(A2:A1500<=DATEVALUE("10/31/2008)),1,0))
.....I've also tried a couple other ways to formulate it but i keep getting
an error..any help would be appreciated..also worth noting is this is for my
security department..i can see how some might think searching for the word
homicide would be odd..haha