Problem with Range

[Mike]

Hello All,

Using Windows & Excel XP.

I have a worksheet that has times located in every other column, A1:A30,
C1:C30, E1:E30. I then name the range "times". I want to find the count of
times that are between 0:30:00 and 0:39:59 (30 and 39:59 minutes). I write
the formula:
=COUNTIF(times,">=" & TIME(0,30,0)) - COUNTIF(times,">=" & TIME(0,39,59))
but get the error #VALUE!

I have tried writing a formula for times in one column and consecutive
columns and it gives the correct count, it is just when the times in every
other column that the formula doesn't work. I would apprecicate any help in
getting the formula to work for the times located in every other column.

Thanks in advance,
Michael

 

[Bob Umlas]

Name the whole range "Times" (A1:E30), then use this formula:
=SUMPRODUCT((Times>=TIME(0,30,0))*(Times<=TIME(0,39,59))*(MOD(COLUMN(Times),
2)=1))
which will pick up every other column.

Bob Umlas
Excel MVP

 

[Mike]

Thanks Bob, works great!!
Mike

Name the whole range "Times" (A1:E30), then use this formula:
=SUMPRODUCT((Times>=TIME(0,30,0))*(Times<=TIME(0,39,59))*(MOD(COLUMN(Times),
2)=1))
which will pick up every other column.

Bob Umlas
Excel MVP