Problem with Range

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

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" <[email protected]> wrote in message
news:x05Qd.63290$jn.5858@lakeread06...
> 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
>
>

Thanks Bob, works great!!
Mike
"Bob Umlas" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:x05Qd.63290$jn.5858@lakeread06...
>> 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
>>
>>
>
>