COUNTIF use with fields of time

S

sshakley

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?
 
L

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?
Click to expand...

=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
 
S

sshakley

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?
 
V

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")))
Click to expand...

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.
 
B

bpeltzer

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
 
Top