Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count cells in column that contain number in a range
I have a column of times in 24 hour format. I want to have a formula that
counts the number of cells in the column that contain a time in a certain range. That is if the cell contains a time between 22:00 and 23:00 the cell should be counted. I will have to do that for each hour. I am trying to determine how many times during the night events happen in each time period. So I want to have a formula that counts the cells that fall within the time periods. I have tried =COUNTIF(I47:I3846,"23:01:<24:00")but it just returns zeros and I know there are cells with times in that range. All help / suggestions / solutions / jokes appreciated :) -- If you don't believe in Freedom of Speech for people you despise ... you don't believe in it at all. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count cells in column that contain number in a range
az willie wrote:
I have a column of times in 24 hour format. I want to have a formula that counts the number of cells in the column that contain a time in a certain range. That is if the cell contains a time between 22:00 and 23:00 the cell should be counted. I will have to do that for each hour. I am trying to determine how many times during the night events happen in each time period. So I want to have a formula that counts the cells that fall within the time periods. I have tried =COUNTIF(I47:I3846,"23:01:<24:00")but it just returns zeros and I know there are cells with times in that range. All help / suggestions / solutions / jokes appreciated :) You could use a PivotTable. Put the Times in the Row and Data Items areas. In the Row area, group by Hours. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count cells in column that contain number in a range
Try this
format as general =COUNTIF(I47:I3846,""&TIME(23,1,0))-COUNTIF(I47:I3846,"<"&TIME(24,0,0)) Mike "az willie" wrote: I have a column of times in 24 hour format. I want to have a formula that counts the number of cells in the column that contain a time in a certain range. That is if the cell contains a time between 22:00 and 23:00 the cell should be counted. I will have to do that for each hour. I am trying to determine how many times during the night events happen in each time period. So I want to have a formula that counts the cells that fall within the time periods. I have tried =COUNTIF(I47:I3846,"23:01:<24:00")but it just returns zeros and I know there are cells with times in that range. All help / suggestions / solutions / jokes appreciated :) -- If you don't believe in Freedom of Speech for people you despise ... you don't believe in it at all. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count cells in column that contain number in a range
?B?TWlrZSBI?= wrote in
: =COUNTIF(I47:I3846,""&TIME(23,1,0))-COUNTIF(I47:I3846,"<"&TIME(24,0,0)) ================= That comes real close to working. I get a count but some of the numbers are negative!!! And, the numbers generated add up to way more than the actual count. I had a column for events and one for the time and I summed the event column and got 399 but some of the hour counts are almost that high. I am < really puzzled at getting negative numbers for some hours. The 10 pm to 11 pm and the 6 am to 7am and 7 am to 8 am generate negative numbers. I am still doing something wrong because I am getting too high a count. I can't seem to get the formulas to paste correctly. I copied two colums. One column has the formula and the next has the time span. When I paste it, everything is getting mashed together. But I think you can see the formulas ok. What have I done wrong here? =COUNTIF(I4:I3846,""&TIME(22,1,0))-COUNTIF(I4:I3846,"<"&TIME(23,0,0)) 10 PM TO 11 PM =COUNTIF(I4:I3846,""&TIME(23,1,0))-COUNTIF(I4:I3846,"<"&TIME(24,0,0)) 11PM TO 12 PM =COUNTIF(I4:I3846,""&TIME(0,1,0))-COUNTIF(I4:I3846,"<"&TIME(1,0,0)) 12PM TO 1 AM =COUNTIF(I4:I3846,""&TIME(1,1,0))-COUNTIF(I4:I3846,"<"&TIME(2,0,0)) 1 AM TO 2 AM =COUNTIF(I4:I3846,""&TIME(2,1,0))-COUNTIF(I4:I3846,"<"&TIME(3,0,0)) 2 AM TO 3 AM =COUNTIF(I4:I3846,""&TIME(3,1,0))-COUNTIF(I4:I3846,"<"&TIME(4,0,0)) 3 AM TO 4 AM =COUNTIF(I4:I3846,""&TIME(4,1,0))-COUNTIF(I4:I3846,"<"&TIME(5,0,0)) 4 AM TO 5 AM =COUNTIF(I4:I3846,""&TIME(5,1,0))-COUNTIF(I4:I3846,"<"&TIME(6,0,0)) 5 AM TO 6 AM =COUNTIF(I4:I3846,""&TIME(6,1,0))-COUNTIF(I4:I3846,"<"&TIME(7,0,0)) 6 AM TO 7 AM =COUNTIF(I4:I3846,""&TIME(7,1,0))-COUNTIF(I4:I3846,"<"&TIME(8,0,0)) 7 AM TO 8 AM -- If you don't believe in Freedom of Speech for people you despise ... you don't believe in it at all. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count cells in column that contain number in a range
Need some clarification...
Usually, when people say count between 10pm and 11pm what they really mean is count *from* 10pm to 11pm. Counting "from" means to include both 10pm and 11pm. Counting "between" technically means to exclude both 10pm and 11pm. -- Biff Microsoft Excel MVP "az willie" wrote in message ... ?B?TWlrZSBI?= wrote in : =COUNTIF(I47:I3846,""&TIME(23,1,0))-COUNTIF(I47:I3846,"<"&TIME(24,0,0)) ================= That comes real close to working. I get a count but some of the numbers are negative!!! And, the numbers generated add up to way more than the actual count. I had a column for events and one for the time and I summed the event column and got 399 but some of the hour counts are almost that high. I am < really puzzled at getting negative numbers for some hours. The 10 pm to 11 pm and the 6 am to 7am and 7 am to 8 am generate negative numbers. I am still doing something wrong because I am getting too high a count. I can't seem to get the formulas to paste correctly. I copied two colums. One column has the formula and the next has the time span. When I paste it, everything is getting mashed together. But I think you can see the formulas ok. What have I done wrong here? =COUNTIF(I4:I3846,""&TIME(22,1,0))-COUNTIF(I4:I3846,"<"&TIME(23,0,0)) 10 PM TO 11 PM =COUNTIF(I4:I3846,""&TIME(23,1,0))-COUNTIF(I4:I3846,"<"&TIME(24,0,0)) 11PM TO 12 PM =COUNTIF(I4:I3846,""&TIME(0,1,0))-COUNTIF(I4:I3846,"<"&TIME(1,0,0)) 12PM TO 1 AM =COUNTIF(I4:I3846,""&TIME(1,1,0))-COUNTIF(I4:I3846,"<"&TIME(2,0,0)) 1 AM TO 2 AM =COUNTIF(I4:I3846,""&TIME(2,1,0))-COUNTIF(I4:I3846,"<"&TIME(3,0,0)) 2 AM TO 3 AM =COUNTIF(I4:I3846,""&TIME(3,1,0))-COUNTIF(I4:I3846,"<"&TIME(4,0,0)) 3 AM TO 4 AM =COUNTIF(I4:I3846,""&TIME(4,1,0))-COUNTIF(I4:I3846,"<"&TIME(5,0,0)) 4 AM TO 5 AM =COUNTIF(I4:I3846,""&TIME(5,1,0))-COUNTIF(I4:I3846,"<"&TIME(6,0,0)) 5 AM TO 6 AM =COUNTIF(I4:I3846,""&TIME(6,1,0))-COUNTIF(I4:I3846,"<"&TIME(7,0,0)) 6 AM TO 7 AM =COUNTIF(I4:I3846,""&TIME(7,1,0))-COUNTIF(I4:I3846,"<"&TIME(8,0,0)) 7 AM TO 8 AM -- If you don't believe in Freedom of Speech for people you despise ... you don't believe in it at all. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count cells in column that contain number in a range
"T. Valko" wrote in
: Need some clarification... Usually, when people say count between 10pm and 11pm what they really mean is count *from* 10pm to 11pm. Counting "from" means to include both 10pm and 11pm. Counting "between" technically means to exclude both 10pm and 11pm. =================== Yes I understand what you're asking I believe. I want to count the first hour from 10:00PM to 11:00 pm. Then the second hour would go from 11:01pm to 12:00pm. Then from 00.01 AM to 01:00 am. If an event happened at 10:00pm it would be counted in the first hour as would an event at 11:00pm. Ah I think I see a fallacy in my thinking. It should probably go from 10:00pm to 10:59pm then from 11:00 to 11:59pm etc. etc. That may be why the results are so high. Events are possibly being double counted. But I don't understand the negative numbers being returned in 3 cases. Hmmmm. -- If you don't believe in Freedom of Speech for people you despise ... you don't believe in it at all. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count cells in column that contain number in a range
az willie wrote in
: "T. Valko" wrote in : Need some clarification... Usually, when people say count between 10pm and 11pm what they really mean is count *from* 10pm to 11pm. Counting "from" means to include both 10pm and 11pm. Counting "between" technically means to exclude both 10pm and 11pm. =================== Yes I understand what you're asking I believe. I want to count the first hour from 10:00PM to 11:00 pm. Then the second hour would go from 11:01pm to 12:00pm. Then from 00.01 AM to 01:00 am. If an event happened at 10:00pm it would be counted in the first hour as would an event at 11:00pm. Ah I think I see a fallacy in my thinking. It should probably go from 10:00pm to 10:59pm then from 11:00 to 11:59pm etc. etc. That may be why the results are so high. Events are possibly being double counted. But I don't understand the negative numbers being returned in 3 cases. Hmmmm. =============== Okay I changed the formulas so they go from 10:00 to 10:59 etc. etc. and now I've got five of them returning negative numbers!! Very strange. I've tried to check the formulas very very carefully and I see no difference between the ones that generate positive numbers and the ones that generate negative numbers. But obviously something is wrong somewhere. Good thing I'm bald already ... this could make a guy tear his hair out :) :) -- If you don't believe in Freedom of Speech for people you despise ... you don't believe in it at all. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count cells in column that contain number in a range
I'm assuming you deal with precision to the minute and not to the second.
Ok, to count *from* 10pm to 10:59pm: =COUNTIF(I47:I3846,"="&TIME(22,0,0))-COUNTIF(I47:I3846,""&TIME(22,59,0)) Better if you put the time boundaries in cells: A1 = 10:00 PM (or 22:00) B1 = 10:59 PM (or 22:59) =COUNTIF(I47:I3846,"="&A1)-COUNTIF(I47:I3846,""&B1) -- Biff Microsoft Excel MVP "az willie" wrote in message ... az willie wrote in : "T. Valko" wrote in : Need some clarification... Usually, when people say count between 10pm and 11pm what they really mean is count *from* 10pm to 11pm. Counting "from" means to include both 10pm and 11pm. Counting "between" technically means to exclude both 10pm and 11pm. =================== Yes I understand what you're asking I believe. I want to count the first hour from 10:00PM to 11:00 pm. Then the second hour would go from 11:01pm to 12:00pm. Then from 00.01 AM to 01:00 am. If an event happened at 10:00pm it would be counted in the first hour as would an event at 11:00pm. Ah I think I see a fallacy in my thinking. It should probably go from 10:00pm to 10:59pm then from 11:00 to 11:59pm etc. etc. That may be why the results are so high. Events are possibly being double counted. But I don't understand the negative numbers being returned in 3 cases. Hmmmm. =============== Okay I changed the formulas so they go from 10:00 to 10:59 etc. etc. and now I've got five of them returning negative numbers!! Very strange. I've tried to check the formulas very very carefully and I see no difference between the ones that generate positive numbers and the ones that generate negative numbers. But obviously something is wrong somewhere. Good thing I'm bald already ... this could make a guy tear his hair out :) :) -- If you don't believe in Freedom of Speech for people you despise ... you don't believe in it at all. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count cells in column that contain number in a range
"T. Valko" wrote in news:uGK4ywwWJHA.5272
@TK2MSFTNGP04.phx.gbl: I'm assuming you deal with precision to the minute and not to the second. Ok, to count *from* 10pm to 10:59pm: =COUNTIF(I47:I3846,"="&TIME(22,0,0))-COUNTIF(I47:I3846,""&TIME(22,59,0)) Better if you put the time boundaries in cells: A1 = 10:00 PM (or 22:00) B1 = 10:59 PM (or 22:59) =COUNTIF(I47:I3846,"="&A1)-COUNTIF(I47:I3846,""&B1) =========== Sorry for the delay in responding. Had a minor surgery yesterday on my foot and the anesthesia had me knocked out all day and still not functioning real good. I will check out using cells as you showed when I get back up to par. Your help is much appreciated. I just started this project at a bad time. Didn't realize the minor surgery would knock me out so bad. Not to mention the owie and the pain pills that don't seem to do too much. Oh well ... this too shall pass and I will conquer the evil spreadsheet. :) -- If you don't believe in Freedom of Speech for people you despise ... you don't believe in it at all. |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count cells in column that contain number in a range
Sounds like you won't be running in a marathon any time soon!
Good luck on your recovery. Here's yet another option: =SUMPRODUCT(--(HOUR(I47:I3846)=22)) That covers from 10:00:00 PM to 10:59:59 PM Just be aware that from 12:00:00 AM to 12:59:59 AM is hour 0. That will also evaluate empty cells as 0 and could be counted as hour 0. To account for that: =SUMPRODUCT(--(I47:I3846<""),--(HOUR(I47:I3846)=22)) -- Biff Microsoft Excel MVP "az willie" wrote in message ... "T. Valko" wrote in news:uGK4ywwWJHA.5272 @TK2MSFTNGP04.phx.gbl: I'm assuming you deal with precision to the minute and not to the second. Ok, to count *from* 10pm to 10:59pm: =COUNTIF(I47:I3846,"="&TIME(22,0,0))-COUNTIF(I47:I3846,""&TIME(22,59,0)) Better if you put the time boundaries in cells: A1 = 10:00 PM (or 22:00) B1 = 10:59 PM (or 22:59) =COUNTIF(I47:I3846,"="&A1)-COUNTIF(I47:I3846,""&B1) =========== Sorry for the delay in responding. Had a minor surgery yesterday on my foot and the anesthesia had me knocked out all day and still not functioning real good. I will check out using cells as you showed when I get back up to par. Your help is much appreciated. I just started this project at a bad time. Didn't realize the minor surgery would knock me out so bad. Not to mention the owie and the pain pills that don't seem to do too much. Oh well ... this too shall pass and I will conquer the evil spreadsheet. :) -- If you don't believe in Freedom of Speech for people you despise ... you don't believe in it at all. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count number of cells and total in one column, based on another column suffix | Excel Worksheet Functions | |||
how to count number of highlighted cells in column | Excel Discussion (Misc queries) | |||
how do I count the number of gray-shaded cells within a range? | Excel Worksheet Functions | |||
count number of cells in range showing between 320 and 345 | Excel Worksheet Functions | |||
Count cells based on date range in another column | New Users to Excel |