Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using Countif to count occurences of time?
Hi..i am trying to get totals from data and am using 2 different times to
count from (eg. i am trying to find how many cells contain times in the 6:00 am to 2:30 pm range in the same column...I can't seem to be able to count these correct Any help is appreciated |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using Countif to count occurences of time?
Try this solution:
=SUMPRODUCT(--(A2:A6TIMEVALUE("6:00")),--(A2:A6<TIMEVALUE("14:30"))) Regards, Stefi €˛Liketoknow€¯ ezt Ć*rta: Hi..i am trying to get totals from data and am using 2 different times to count from (eg. i am trying to find how many cells contain times in the 6:00 am to 2:30 pm range in the same column...I can't seem to be able to count these correct Any help is appreciated |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using Countif to count occurences of time?
I forgot to tell you that A2:A7 is only an example, change it as necessary!
Stefi €˛Liketoknow€¯ ezt Ć*rta: Hi..i am trying to get totals from data and am using 2 different times to count from (eg. i am trying to find how many cells contain times in the 6:00 am to 2:30 pm range in the same column...I can't seem to be able to count these correct Any help is appreciated |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using Countif to count occurences of time?
Thanks..i will try it
"Stefi" wrote: I forgot to tell you that A2:A7 is only an example, change it as necessary! Stefi €˛Liketoknow€¯ ezt Ć*rta: Hi..i am trying to get totals from data and am using 2 different times to count from (eg. i am trying to find how many cells contain times in the 6:00 am to 2:30 pm range in the same column...I can't seem to be able to count these correct Any help is appreciated |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using Countif to count occurences of time?
Stefi...thanks for the help...works great but i do have 1 problem..when i try
and count from 22:30 pm to 6:00 am i get 0 ...any ideas? i have checked all the formula and it is exactly as the other (with time changes of course tks "Liketoknow" wrote: Thanks..i will try it "Stefi" wrote: I forgot to tell you that A2:A7 is only an example, change it as necessary! Stefi €˛Liketoknow€¯ ezt Ć*rta: Hi..i am trying to get totals from data and am using 2 different times to count from (eg. i am trying to find how many cells contain times in the 6:00 am to 2:30 pm range in the same column...I can't seem to be able to count these correct Any help is appreciated |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using Countif to count occurences of time?
Yes, because XL stores time values as a fraction, e.g. 6:00 is stored as 0.25
(that is 6/24), 22:30 as 0.9375 (that is 22.5/24), and XL doesn't know that you mean start time 22:30 toDAY, but end time 6:00 toMORROW, unless you specify it explicitely. One way of specifying it is using date + time, e.g.: If A2: today's date 22:30 A3: today's date 23:30 A4: tomorrow's date 2:30 A5: tomorrow's date 3:30 A6: tomorrow's date 6:30 then in A7 =SUMPRODUCT(--(A2:A6TODAY()+TIMEVALUE("22:30")),--(A2:A6<TODAY()+1+TIMEVALUE("6:00"))) returns the correct answer, that is 3. Regards, Stefi €˛Liketoknow€¯ ezt Ć*rta: Stefi...thanks for the help...works great but i do have 1 problem..when i try and count from 22:30 pm to 6:00 am i get 0 ...any ideas? i have checked all the formula and it is exactly as the other (with time changes of course tks "Liketoknow" wrote: Thanks..i will try it "Stefi" wrote: I forgot to tell you that A2:A7 is only an example, change it as necessary! Stefi €˛Liketoknow€¯ ezt Ć*rta: Hi..i am trying to get totals from data and am using 2 different times to count from (eg. i am trying to find how many cells contain times in the 6:00 am to 2:30 pm range in the same column...I can't seem to be able to count these correct Any help is appreciated |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Entering Times | Excel Discussion (Misc queries) | |||
In COUNTIF function, how do I count the number of cells which con. | Excel Worksheet Functions | |||
countif number of occurences per month per year. | Excel Worksheet Functions | |||
count number of occurences within a string | Excel Worksheet Functions | |||
Count occurences between dates | Excel Worksheet Functions |