Array Formula

[ddd2009]

=SUM(IF((YEAR(P4)=YEAR(LS510!$A$1:$A$100))+(MONTH(P4)=MONTH(LS510!$A$1:$A$100)),LS510!$E$1:E$17)). I am trying to create an array formula, my attempt above is to look at the date (February 2014) in one worksheet and calculate the total from another, as per the table below. I have formatted the dates in both worksheets to be the same. My formula above is calculating the total amount in column E where as it should be £6204.65 and wont allow me to control/alt/enter? Thank you in advance any help will be greatly appreciated.

	B	C	D	E
01/02/2014	1			1,862.74
08/02/2014	2			2,273.69
22/02/2014	3			2,068.22
01/03/2014	4			2,273.69
08/03/2014	5		1,945.77
15/03/2014	6		2,472.47
22/03/2014	7		2,567.28
29/03/2014	8		2,209.12
05/04/2014	9		796.61
		0.00	9,991.25	8,478.34

<tbody>
</tbody>

 

[StefanVL]

let's say data starts at A1 and dates start at A2. I hold my february 1st 2014 in H1 in this case:

=SUMPRODUCT(E2:E10,--(A2:A10-DAY(A2:A10)+1=H1))

 

[FormR]

Another option:

Confirmed with CSE:
=SUM(IF(TEXT('LS510'!A1:A100,"YYYYMM")=TEXT(P4,"YYYYMM"),'LS510'!E1:E100))

or confirmed normally:
=SUMPRODUCT(--(TEXT('LS510'!A1:A100,"YYYYMM")=TEXT(P4,"YYYYMM")),'LS510'!E1:E100)

 

[FormR]

And one more option for you to try:

=SUMIFS('LS510'!E1:E100,'LS510'!A1:A100,">="&P4,'LS510'!A1:A100,"<"&EOMONTH(P4,0))

 

[ddd2009]

[Thank you I used the latter and works perfectly]Another option:

Confirmed with CSE:
=SUM(IF(TEXT('LS510'!A1:A100,"YYYYMM")=TEXT(P4,"YYYYMM"),'LS510'!E1:E100))

or confirmed normally:
=SUMPRODUCT(--(TEXT('LS510'!A1:A100,"YYYYMM")=TEXT(P4,"YYYYMM")),'LS510'!E1:E100)[/QUOTE]