Formula question
 

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

Formula question
 

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" wrote in message
...
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

Formula question
 

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" wrote in message
...
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

Formula question
 

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)*(C57: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

Formula question
 

=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" wrote in message
...
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

Formula question
 

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)*(C57: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