Formula question

[Guest]

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

 

[Max]

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")
)

 

[Roger Govier]

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)"

 

[Guest]

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

 

[Bob Phillips]

=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)

 

[Guest]

thanks to you ALL for the help/suggestions.
I'll give them a go and hope I get the result required
many thanks