| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Hour Count
I'm trying to count the number of times a particular hour appears on my
spreadsheet. For example I have a spreadsheet listing dates and times. I created an =MOD function to only display the time (hh:mm:ss, military time). I would like to count the number of times a particular hour appears on the spreadshe, ie how many times the hour "7", appears then "8" and so on and so on until all hours are acccounted for. I tried using =COUNTIF(B2:B520,"7*") for the the 7am hour, but the results comes up as "0". Can anyone help with this. e |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Hour Count
Try
=SUMPRODUCT(--(HOUR(B2:B250)=7)) -- Regards, Peo Sjoblom "EliA" wrote in message ... I'm trying to count the number of times a particular hour appears on my spreadsheet. For example I have a spreadsheet listing dates and times. I created an =MOD function to only display the time (hh:mm:ss, military time). I would like to count the number of times a particular hour appears on the spreadshe, ie how many times the hour "7", appears then "8" and so on and so on until all hours are acccounted for. I tried using =COUNTIF(B2:B520,"7*") for the the 7am hour, but the results comes up as "0". Can anyone help with this. e |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Hour Count
Hi
=COUNT(IF(HOUR(B2:B520)=7,B2:B520,FALSE)) Which is an array so commit with Ctrl+Shift+Enter Mike "EliA" wrote: I'm trying to count the number of times a particular hour appears on my spreadsheet. For example I have a spreadsheet listing dates and times. I created an =MOD function to only display the time (hh:mm:ss, military time). I would like to count the number of times a particular hour appears on the spreadshe, ie how many times the hour "7", appears then "8" and so on and so on until all hours are acccounted for. I tried using =COUNTIF(B2:B520,"7*") for the the 7am hour, but the results comes up as "0". Can anyone help with this. e |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Hour Count
Thanks alot. That worked!!!
"Peo Sjoblom" wrote: Try =SUMPRODUCT(--(HOUR(B2:B250)=7)) -- Regards, Peo Sjoblom "EliA" wrote in message ... I'm trying to count the number of times a particular hour appears on my spreadsheet. For example I have a spreadsheet listing dates and times. I created an =MOD function to only display the time (hh:mm:ss, military time). I would like to count the number of times a particular hour appears on the spreadshe, ie how many times the hour "7", appears then "8" and so on and so on until all hours are acccounted for. I tried using =COUNTIF(B2:B520,"7*") for the the 7am hour, but the results comes up as "0". Can anyone help with this. e |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Hour Count
=SUM(COUNTIF(A:A,{"=7:00","8:00"})*{1,-1})
"EliA" wrote: I'm trying to count the number of times a particular hour appears on my spreadsheet. For example I have a spreadsheet listing dates and times. I created an =MOD function to only display the time (hh:mm:ss, military time). I would like to count the number of times a particular hour appears on the spreadshe, ie how many times the hour "7", appears then "8" and so on and so on until all hours are acccounted for. I tried using =COUNTIF(B2:B520,"7*") for the the 7am hour, but the results comes up as "0". Can anyone help with this. e |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Overtime for 8 hour and 40 hour | Excel Discussion (Misc queries) | |||
| How to count hours- and multiply with my wage per hour... | Excel Discussion (Misc queries) | |||
| Count by date and hour | Excel Worksheet Functions | |||
| convert decimal numbers to a fraction of an hour for payroll hour | Excel Worksheet Functions | |||
| How can I round an hour to the nearest 1/4 hour? | Excel Worksheet Functions | |||
Linear Mode
