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

I have a table in one worksheet that details health and safety 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 top (Jan-Dec columns B:M) and Category 1-10 down column A (A2:A11). This table drives graphs that detail occurrences by month of each type of incident.

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?

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

Any assistance would be greatly received

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
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Hi KH3557

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

Try the following sumproduct formula on sheet2 in cell B2 copy across 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

Works like a dream, many thanks, much appreciated