Count the number of times a cell value is within a specific range

[Guest]

I am looking for a formula that will count the number of times a specific
task (in column J) occurs within a given time range (in column E).

Example, how many times does "A" occur between 0800 hrs. and 0900 hrs.

I also need a second formula that sorts the above for each employee.

The times are currently being inputed as a number 0800, 0812, etc and not
actually military times.

Thanks for any assistance I may get.

 

[Don Guillett]

try using sumproduct
=sumproduct((rngA>criteria)*(rngA<criteria))

 

[Guest]

That did not help.

Let me further explain what I am trying to accomplish. I am a crime scene
investigator. Investigators take various calls, such as assaults. The
receive the calls at various times throught the day. I am trying to count
the number of times the entire unit responded to an assault call between 0800
hrs. and 0900 hrs.

I then want to count the number of times an individual investigator took the
same call between 0800 hrs. and 0900 hrs.

There are 26 different types of calles we respond to through out the day.
The types of calls are in column J. The times I am wanting to use in the
counting criteria are in column E.

Everett

 

[Guest]

This may also help

Problem 1:
Column E Column J
0923 Assault
0945 ADW
1159 Assault
0901 Assault

How many "Assault" calls were there between 0900 and 1000?

Problem2:
Column B Column E Column J
Bill 0923 Assault
Jim 0945 ADW
Bill 1159 Assault
Adam 0901 Assault

How many "Assault" calls did Bill take between 0900 hrs. and 1000 hrs.?

 

[Sandy Mann]

Don has already given you an answer:

Problem 1:
Column E Column J
0923 Assault
0945 ADW
1159 Assault
0901 Assault

How many "Assault" calls were there between 0900 and 1000?
=SUMPRODUCT((J1:J4="Assault")*(E1:E4>900)*(E1:E4<1000))

Problem2:
Column B Column E Column J
Bill 0923 Assault
Jim 0945 ADW
Bill 1159 Assault
Adam 0901 Assault

How many "Assault" calls did Bill take between 0900 hrs. and 1000 hrs.?

=SUMPRODUCT((B1:B4="Bill")*(E1:E4>900)*(E1:E4<1000)*(J1:J4="Assault"))


--
HTH

Sandy
In Perth, the ancient capital of Scotland

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk