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

E

Everett

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.
 
E

Everett

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
 
E

Everett

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.?
 
S

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.?
Click to expand...

=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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

counting the occurrence of specific text within a date range 4
Is there a function to count how many times a cells value is used? 0
Counting multiple occcurrences between date range 3
Counting the number of times a character occurs in a cell 1
Count the times a specific character appears in a cell 3
Trying to count based on multiple criteria & cell comparisons 4
Counting number of times name occurs between two dates 2
How to count the number of times a number is repeated in a column 2

Top