Formula question-when a number in a column is found.

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:$C1000<--"12:00"))


but don't know how to ask for a time between certain values....

can anybody help a novice in distress !!

Many thanks

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

Try:

=SUMPRODUCT(($B$5:$B1000=50)*($C$5:$C1000> --"7:31")*($C$5:$C1000< --"7:59")
)

--
Max, Singapore GMT+8, xl97
Samples archive at: http://savefile.com/projects/236895
xdemechanik
--
"Anthony" <[email protected]> wrote in message
news:[email protected]...
> 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:$C1000<--"12:00"))
>
>
> but don't know how to ask for a time between certain values....
>
> can anybody help a novice in distress !!
>
> Many thanks

Hi Anthony

Try
=SUMPRODUCT(--($B$5:$B1000=(50))*($C$5:$C1000<=TIME(7,59,0)*($C$5:$C1000>=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 " " in the formula ="(50)"

--
Regards

Roger Govier


"Anthony" <[email protected]> wrote in message
news:[email protected]...
> 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:$C1000<--"12:00"))
>
>
> but don't know how to ask for a time between certain values....
>
> can anybody help a novice in distress !!
>
> 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)*(C5>7:31)*(C5<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 Student


"Anthony" wrote:

> 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:$C1000<--"12:00"))
>
>
> but don't know how to ask for a time between certain values....
>
> can anybody help a novice in distress !!
>
> Many thanks

=SUMPRODUCT(--($B$5:$B1000=50),--($C$5:$C1000>=--"07:31"),--($C$5:$C1000<=--
"07:59"))


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Anthony" <[email protected]> wrote in message
news:[email protected]...
> 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:$C1000<--"12:00"))
>
>
> but don't know how to ask for a time between certain values....
>
> can anybody help a novice in distress !!
>
> 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

"Gary''s Student" wrote:

> 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)*(C5>7:31)*(C5<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 Student
>
>
> "Anthony" wrote:
>
> > 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:$C1000<--"12:00"))
> >
> >
> > but don't know how to ask for a time between certain values....
> >
> > can anybody help a novice in distress !!
> >
> > Many thanks