Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting for multiple conditions | Excel Worksheet Functions | |||
Help with counting multiple conditions | Excel Discussion (Misc queries) | |||
Counting based upon 2 conditions that are text based | Excel Discussion (Misc queries) | |||
Cell values based upon multiple conditions | New Users to Excel | |||
counting based on 2 conditions | Excel Discussion (Misc queries) |