Counting multiple occcurrences between date range

[KH3557]

I was wondering if anyone could assist me with an excel problem.

I have a table in one worksheet that details health and safet
incidents. The data captured includes:


Column A: Date of Incident

Column B: Category of Incident – Cat 1 = slip, trip or fall, cat 2
physical assault and so on up to cat 10.

Column C: Summary


On a separate worksheet I have a table split by month across the to
(Jan-Dec columns B:M) and Category 1-10 down column A (A2:A11). Thi
table drives graphs that detail occurrences by month of each type o
incident.

I was wondering if there is a way to count the number of times eac
category occurs between date ranges that will automatically populate th
table that drives the graphs?

I was thinking that a countif formula within the table would be the bes
solution with the formula amended in each row and column to reflec
month and category.

Any assistance would be greatly receive

 

[Claus Busch]

Hi,

Am Thu, 25 Apr 2013 16:12:58 +0100 schrieb KH3557:

Column A: Date of Incident

Column B: Category of Incident – Cat 1 = slip, trip or fall, cat 2 =
physical assault and so on up to cat 10.

Column C: Summary

I was wondering if there is a way to count the number of times each
category occurs between date ranges that will automatically populate the
table that drives the graphs?

for Cat 1 between 1/1/13 and 3/31/13 try:
=SUMPRODUCT(--($A$2:$A$1000>=DATE(2013,1,1)),--($A$2:$A$1000<=DATE(2013,3,31)),--($B$2:$B$1000="Cat 1"))



Regards
Claus Busch

 

[Kevin@Radstock]

Hi KH3557

If the dates in B1:M1 are proper dates and formatted as "mmm" and th
year =2013

Try the following sumproduct formula on sheet2 in cell B2 copy acros
and down. Format as something simple "General;;" to remove zero values
Adjust the sheet and ranges to suit your needs.

=SUMPRODUCT(--(MONTH(Sheet1!$A$2:$A$100)=MONTH(B$1)),--(Sheet1!$B$2:$B$100=$A2))

Kevin

 

[KH3557]

Works like a dream, many thanks, much appreciated