Counting values based on multiple conditions

[Paul]

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

 

[Bernard Liengme]

=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

 

[Mike H]

Hi,

Try this

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

all one line


Mike

 

[Paul]

worked perfectly...happy holidays

paul

Hi,

Try this

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

all one line


Mike

 

[Mike H]

Your welcome, glad I could help

worked perfectly...happy holidays

paul