PDA

View Full Version : Formulas



KK1966
07-19-2010, 11:57 PM
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.

Bob Phillips
07-20-2010, 12:57 AM
Try

=COUNTIF(A:A,"<10:30:00")

=COUNTIF(A:A,"<12:30:00")-COUNTIF(A:A,"<10:30:00")

KK1966
07-20-2010, 01:10 AM
Try

=COUNTIF(A:A,"<10:30:00")

=COUNTIF(A:A,"<12:30:00")-COUNTIF(A:A,"<10:30:00")


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 ?

Bob Phillips
07-20-2010, 02:09 AM
Then why is <10:30 returning 4 in your example?

KK1966
07-20-2010, 02:15 AM
Then why is <10:30 returning 4 in your example?


I'm so sorry that;s misleaded the message,
actully <10:31 is correct.

sorry XLD.
can it help ?

Bob Phillips
07-20-2010, 02:18 AM
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

KK1966
07-20-2010, 02:21 AM
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


Wow ,,, thanks very much