Select data between dates and display them by month

[Jamie_QHSE]

I would like to select data between dates and display them by month. For example in E6 should contain a 1 as per the List in rows A and B. I used a SUMPRODUCT to get the total for a month for a whole list but not for a reporting period. Is this possible? Thank you.

[AliGW]

I do not see your SUMPRODUCT formula.

[Jamie_QHSE]

I have re uploaded the correct version - Please refer Column H

[AliGW]

Step one (for learning purposes) in E6 copied across and down:

=SUMPRODUCT(--(MONTH($A$2:$A$17)=COLUMNS($E5:E5))*($B$2:$B$17=$D6))

Now adapt it to this in E6 copied across and down:

=IF(AND(MONTH($E$2)>=COLUMNS($E5:E5),MONTH($E$3)<=COLUMNS($E5:E5)),SUMPRODUCT(--(MONTH($A$2:$A$17)=COLUMNS($E5:E5))*($B$2:$B$17=$D6)),"")

[Jamie_QHSE]

Thank you, is there a way to make this work to populate the data across multiple months if the reporting period is 17-Jan-17 and 1/09/2017.

[AliGW]

That's what it does. Did you try it?

EDIT: Oops! My mistake - try it the other way around:

=IF(AND(COLUMNS($E5:E5)>=MONTH($E$2),COLUMNS($E5:E5)<=MONTH($E$3)),SUMPRODUCT(--(MONTH($A$2:$A$17)=COLUMNS($E5:E5))*($B$2:$B$17=$D6)),"")

[davsth]

Perhaps
=SUMPRODUCT(($A$2:$A$17>=$E$2)*($A$2:$A$17<=$E$3)*($B$2:$B$17=$D6)*(TEXT($A$2:$A$17,"mmm")=E$5)) in in cell E6

Although you should change january to jan, to be consistent

[Jamie_QHSE]

I have uploaded a copy with your formula for your reference. As you detailed i copied the formula across and down.

[AliGW]

You used the wrong version - see post #6.

Excel 2016 (Windows) 32 bit

	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	REPORTING DETAILS
2	Enter period Date From	01/01/2017
3	Enter period Date To	01/09/2017
4	TRENDING DATA ALALYSIS REPORTING PERIOD
5	INCIDENT TYPE	January	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Total
6	Personal Injury	1	0	0	2	1	0	0	1	0
7	Plant/ Equipment	1	0	0	1	1	0	0	0	0
8	Electrical	0	0	0	0	0	0	0	0	0
9	Asset Damage	0	1	2	0	0	1	0	1	2
10	Environmental	1	0	0	0	0	0	0	0	0
11	HR	0	0	0	0	0	0	0	0	0
12	Project Management	0	0	0	0	0	0	0	0	0
13	Emergency Scenario	0	0	0	0	0	0	0	0	0
14	Monthly Totals	3	1	2	3	2	1	0	2	2	0	0	0	0

Sheet: Sheet1

Excel 2016 (Windows) 32 bit

	E
6	=IF(AND(COLUMNS($E5:E5)>=MONTH($E$2),COLUMNS($E5:E5)<=MONTH($E$3)),SUMPRODUCT(--(MONTH($A$2:$A$17)=COLUMNS($E5:E5))*($B$2:$B$17=$D6)),"")

Sheet: Sheet1

[Jamie_QHSE]

Thank you for you help, the formula worked perfectly.