COUNTIF use with fields of time

[Guest]

Does anyone know how to use the countif (or a correct funtion) to count the
number of times a "time value" shows up in a series?

For example, the series J1-J100 is made of up time formated fields. The
fields have 1:03AM, 2:15AM, 4:15PM, etc in them. I want to count how many
times 1:**AM shows up in the series.

I am guessing it is something like countif(J1:J100, "1*AM"), but that
doesn't seem to work.

Any ideas?

 

[Linker IT Software]

Does anyone know how to use the countif (or a correct funtion) to count

the
number of times a "time value" shows up in a series?

=COUNTIF(B3:B8;">=0.5")

Note:
In B3:B8 I have typed the time values
0.5 is 12:00 PM. All values above and including 0.5 are PM all values below
0.5 are AM

If you are interested to see our addin full of additional Excel functions,
check out litLIB at www.oraxcel.com/projects/litlib

Hope this helps,

Gerrit-Jan Linker
Linker IT Software
www.oraxcel.com

 

[Vito]

Try:

=SUMPRODUCT((J1:J100>=TIMEVALUE("1:00 AM"))*(J1:J5<TIMEVALUE("2:00
AM")))

 

[Guest]

Vito:
Can you help explain the logic? It looks like you are converting 2am and
1am to decimal, but I am not following the reasoning why the mulitplication
component. (net:it didn't work). Also, why J1:J5 on the 2am side?

 

[Vito]

Sorry, I used a smaller data set to test and forgot to convert back to
J1:J100.

Try this one:

=SUMPRODUCT((J1:J100>=TIMEVALUE("1:00 AM"))*(J1:J100<TIMEVALUE("2:00

AM")))

or

=SUMPRODUCT(--(J1:J100>=TIMEVALUE("1:00
AM")),--(J1:J100<TIMEVALUE("2:00 AM")))

The reason for the * is that Trues and Falses are Booleans like 1's and
0's. Multiplying corresponding Trues and Falses yields 1*1, 1*0, 0*1,
etc which are then summed using sumproduct.

The -- also coerces the Trues and Falses to 1's and 0's in order to
count or sum, etc.

 

[Guest]

Since you're asking for a range of values of a single column (as opposed to
different values from different columns), my preference is to use two countif
functions to create the range:
=countif(a:a,">=" & timevalue(1,0,0)) - countif(a:a,">=" & timevalue(2,0,0))
So you're counting all values that are at/after 1AM, then subtracting all
those that are at/after 2AM. Those remaining are in the 1AM hour.
HTH. --Bruce