HI, All
How do I get formulas help with sum without duplicated time as example below,
Data
9:30
9:31
9:31
9:35
10:30
10:30
10:31
10:33
10:33
10:33
Criteria Resulted
<10:30 4
10:31-12:30 2
Which formulas can do those? Pleased help help me.
HI, All
How do I get formulas help with sum without duplicated time as example below,
Data
9:30
9:31
9:31
9:35
10:30
10:30
10:31
10:33
10:33
10:33
Criteria Resulted
<10:30 4
10:31-12:30 2
Which formulas can do those? Pleased help help me.
Try
=COUNTIF(A:A,"<10:30:00")
=COUNTIF(A:A,"<12:30:00")-COUNTIF(A:A,"<10:30:00")
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
Originally Posted by xld
Hi Xld
Thanks the formulas, but i wants to exclude the duplicated time sample as
10:30
10:30
10:30 count = 1
can it be do ?
Then why is <10:30 returning 4 in your example?
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
Originally Posted by xld
I'm so sorry that;s misleaded the message,
actully <10:31 is correct.
sorry XLD.
can it help ?
Sure, try this array formula
=COUNT(1/FREQUENCY(IF(($A$2:$A$100>--"00:00:00")*($A$2:$A$100<=--"10:30:00"),IF($A$2:$A$100<>"",$A$2:$A$100)),IF(($A$2:$A$100>--"00:00:00")*($A$2:$A$100<=--"10:30:00"),IF($A$2:$A$100<>"",$A$2:$A$100))))
Just change the time thresholds to suit
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
Originally Posted by xld
Wow ,,, thanks very much