|
Statistical Counting
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? |
Use a Pivot Table. It will allow you to group the date field by year and month
(Be sure to do both. You don't want Jan 2003 and Jan 2004 to be added together). On Mon, 31 Jan 2005 20:37:03 -0800, HiDbLevel wrote: 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? |
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? . |
On Mon, 31 Jan 2005 21:24:07 -0800, "Biff" wrote:
I sure hope I'm not in that list! Gee, I didn't realize there was any reason for worry.... <g |
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? . |
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? . . |
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? . . |
I figured it out. For some reason when I took out the <1/31/2005 section
Excel was able to give me the information I wanted. Thanks a bunch for your help with this! HiDbLevel "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? . . |
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? . . . |
| All times are GMT +1. The time now is 11:14 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com