software

Hi all,

I wish to count the number of times that a 'time' falls between certain
values when a number in a column is found.
So in column B I have aprox 1000 lines of data, I want all these to be
seached for a 'certain' number, lets say 50, then each time this number is
found in column B check to see if the corresponding time in column D falls
between certain criteria.

eg check column B for the number 50 and check column C for time between 0731
and 07.59.

I can get this to work for a 'time' before 12:00 like this......

=SUMPRODUCT(--($B$5:$B1000=(50))*($C$5:$C1000lt;--quot;12:00quot;))but don't know how to ask for a time between certain values....

can anybody help a novice in distress !!

Many thanks

gt; eg check column B for the number 50 and
gt; check column C for time between 0731 and 07.59.

Try:

=SUMPRODUCT(($B$5:$B1000=50)*($C$5:$C1000gt; --quot;7:31quot;)*($C$5:$C1000lt; --quot;7:59quot;)
)

--
Max, Singapore GMT 8, xl97
Samples archive at: savefile.com/projects/236895
xdemechanik
--
quot;Anthonyquot; gt; wrote in message
...
gt; Hi all,
gt;
gt; I wish to count the number of times that a 'time' falls between certain
gt; values when a number in a column is found.
gt; So in column B I have aprox 1000 lines of data, I want all these to be
gt; seached for a 'certain' number, lets say 50, then each time this number is
gt; found in column B check to see if the corresponding time in column D falls
gt; between certain criteria.
gt;
gt; eg check column B for the number 50 and check column C for time between
0731
gt; and 07.59.
gt;
gt; I can get this to work for a 'time' before 12:00 like this......
gt;
gt; =SUMPRODUCT(--($B$5:$B1000=(50))*($C$5:$C1000lt;--quot;12:00quot;))
gt;
gt;
gt; but don't know how to ask for a time between certain values....
gt;
gt; can anybody help a novice in distress !!
gt;
gt; Many thanks
Hi Anthony

Try
=SUMPRODUCT(--($B$5:$B1000=(50))*($C$5:$C1000lt;=TIME(7,59,0)*($C$ 5:$C1000gt;=TIME(7,31,0))

TIME(hour,minute,second) is the generalised format.

There is no need for the brackets around the 50 in the first part of the
formula. If you mean -50, then use =-50 rather than (50) even though
your display might be (50), otherwise the formula will only find those
that are positive 50.
If the brackets are a text item, and your cells do contain (50) then you
would need to enclose that within quot; quot; in the formula =quot;(50)quot;

--
Regards

Roger Govierquot;Anthonyquot; gt; wrote in message
...
gt; Hi all,
gt;
gt; I wish to count the number of times that a 'time' falls between
gt; certain
gt; values when a number in a column is found.
gt; So in column B I have aprox 1000 lines of data, I want all these to be
gt; seached for a 'certain' number, lets say 50, then each time this
gt; number is
gt; found in column B check to see if the corresponding time in column D
gt; falls
gt; between certain criteria.
gt;
gt; eg check column B for the number 50 and check column C for time
gt; between 0731
gt; and 07.59.
gt;
gt; I can get this to work for a 'time' before 12:00 like this......
gt;
gt; =SUMPRODUCT(--($B$5:$B1000=(50))*($C$5:$C1000lt;--quot;12:00quot;))
gt;
gt;
gt; but don't know how to ask for a time between certain values....
gt;
gt; can anybody help a novice in distress !!
gt;
gt; Many thanks
SUMPRODUCT is great because it takes only one formula in one cell to get the
result. An alternative approach is simple, but uses a helper column.In an un-used column enter:

=(B5=50)*(C5gt;7:31)*(C5lt;7:59) and copy down. This formula should give 1 only
if all conditions are met. Then just sum the column.

Note that you will probably have to modify the constants in the formula
depending on the format of the data in column C
--
Gary''s Studentquot;Anthonyquot; wrote:

gt; Hi all,
gt;
gt; I wish to count the number of times that a 'time' falls between certain
gt; values when a number in a column is found.
gt; So in column B I have aprox 1000 lines of data, I want all these to be
gt; seached for a 'certain' number, lets say 50, then each time this number is
gt; found in column B check to see if the corresponding time in column D falls
gt; between certain criteria.
gt;
gt; eg check column B for the number 50 and check column C for time between 0731
gt; and 07.59.
gt;
gt; I can get this to work for a 'time' before 12:00 like this......
gt;
gt; =SUMPRODUCT(--($B$5:$B1000=(50))*($C$5:$C1000lt;--quot;12:00quot;))
gt;
gt;
gt; but don't know how to ask for a time between certain values....
gt;
gt; can anybody help a novice in distress !!
gt;
gt; Many thanks

=SUMPRODUCT(--($B$5:$B1000=50),--($C$5:$C1000gt;=--quot;07:31quot;),--($C$5:$C1000lt;=--
quot;07:59quot;))--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;Anthonyquot; gt; wrote in message
...
gt; Hi all,
gt;
gt; I wish to count the number of times that a 'time' falls between certain
gt; values when a number in a column is found.
gt; So in column B I have aprox 1000 lines of data, I want all these to be
gt; seached for a 'certain' number, lets say 50, then each time this number is
gt; found in column B check to see if the corresponding time in column D falls
gt; between certain criteria.
gt;
gt; eg check column B for the number 50 and check column C for time between
0731
gt; and 07.59.
gt;
gt; I can get this to work for a 'time' before 12:00 like this......
gt;
gt; =SUMPRODUCT(--($B$5:$B1000=(50))*($C$5:$C1000lt;--quot;12:00quot;))
gt;
gt;
gt; but don't know how to ask for a time between certain values....
gt;
gt; can anybody help a novice in distress !!
gt;
gt; Many thanks
thanks to you ALL for the help/suggestions.
I'll give them a go and hope I get the result required
many thanks

quot;Gary''s Studentquot; wrote:

gt; SUMPRODUCT is great because it takes only one formula in one cell to get the
gt; result. An alternative approach is simple, but uses a helper column.
gt;
gt;
gt; In an un-used column enter:
gt;
gt; =(B5=50)*(C5gt;7:31)*(C5lt;7:59) and copy down. This formula should give 1 only
gt; if all conditions are met. Then just sum the column.
gt;
gt; Note that you will probably have to modify the constants in the formula
gt; depending on the format of the data in column C
gt; --
gt; Gary''s Student
gt;
gt;
gt; quot;Anthonyquot; wrote:
gt;
gt; gt; Hi all,
gt; gt;
gt; gt; I wish to count the number of times that a 'time' falls between certain
gt; gt; values when a number in a column is found.
gt; gt; So in column B I have aprox 1000 lines of data, I want all these to be
gt; gt; seached for a 'certain' number, lets say 50, then each time this number is
gt; gt; found in column B check to see if the corresponding time in column D falls
gt; gt; between certain criteria.
gt; gt;
gt; gt; eg check column B for the number 50 and check column C for time between 0731
gt; gt; and 07.59.
gt; gt;
gt; gt; I can get this to work for a 'time' before 12:00 like this......
gt; gt;
gt; gt; =SUMPRODUCT(--($B$5:$B1000=(50))*($C$5:$C1000lt;--quot;12:00quot;))
gt; gt;
gt; gt;
gt; gt; but don't know how to ask for a time between certain values....
gt; gt;
gt; gt; can anybody help a novice in distress !!
gt; gt;
gt; gt; Many thanks