Count of type of incident within range of dates - somewhat complex

[MrPerry]

Hello all,

I'm pretty stumped on this. What I am trying to accomplish isn't exact to what is above in the picture but I will explain because the picture is just an example to keep it simple.

I have 2 sheets in my workbook that are needed. One sheet contains a log of incidents with information similar to what is on the left columns in A-F in the picture. The other sheet contains a column for each week and under week is the total of the type of each incident between the last week ending date and the current week. Also, each week ends on a Sunday. So data is from Monday through Sunday. According to the picture, you will notice in blue color is what each row in red should display but I haven't gotten it to show with any functions i've tried so far.

I am trying to eliminate having to manually enter the amount of incidents for each week. Also, FA stands for First Aid and NM stands for Near Miss. So, let's say we had 2 FA's in the log on January 25, 2011. The cell L3 would then display 2 because it counted 2 after the date in K1 and on or before the date in L1.

Also, the reason for all the different styles of date break up on the left is because the log right now only shows the date as the cells in A, C, and D. So, the cells in E are functioned to show what it presently shows. I added that while tinkering with this because I thought excel couldn't read a 3 cell date and needed it in one cell.

I hope this makes sense. Please let me know if you have questions.

 

[Peter_SSs]

1. You should investigate the methods in my signature block for posting small screen shots. You will get a lot more helpers if they can copy your sample data rather than having to type it out themselves.

2. Having the date in a single cell is normally the easiest to deal with so I have used column E.

3. See if this helps. If you have Excel 2007 or later, try the formula in H2 copied across and down. If you have Excel 2003 or earlier, try the one in H5.
You may have to adjust the ranges in formulas before copying.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Month		Day	Year				2/01/11	9/01/11	16/01/11	23/01/11	30/01/11	6/02/11	13/02/11	Total
2	Jan		11	2011	11/01/11	FA	FA	0	0	1	0	2	0	0	3
3	Jan		20	2011	20/01/11	NM	NM	0	0	1	1	0	0	0	2
4	Jan		30	2011	30/01/11	FA
5	Jan		25	2011	25/01/11	FA	FA	0	0	1	0	2	0	0	3
6	Jan		14	2011	14/01/11	NM	NM	0	0	1	1	0	0	0	2
7
Incidents

 

[MrPerry]

Thank you Peter. I was able to duplicate what you've done. However, when I try to implement the same formulas but referencing the date and incident types on a separate sheet it returns only 0's. I'm not sure what I'm still doing wrong.

Here is a sample of a function I ended up with:

=SUMPRODUCT(--('Incident Log'!$G5:$G2000=$A28),--('Incident Log'!$S5:$S2000<=B$2))-SUM($A28:A28)

In the G column on the Incident Log sheet is where the dates are. The S column has the Incident types.

 

[MrPerry]

Nevermind! I figured out my mistake. I had the incident type and date ranges backwards and I also forgot to reference the sheet on the end of both ranges.


Peter, Thank you so much for your help!

 

[Peter_SSs]

No problem, glad you figured it out.
Thanks for the feedback.