Formulas

[KK1966]

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]

Try

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

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

[KK1966]

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]

Then why is <10:30 returning 4 in your example?

[KK1966]

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]

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]

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