Array Formula is just not working

[TheTao2]

Hello All,

I have a workbook with two worksheets. The first is called check book and is basically just to enter in checking account activity. The second worksheet has an array formula that is supposed to find any entries on the first worksheet that fall within the specified month. It keeps returning an error and I can not figure it out. I know I must be missing something silly but I am stumped.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L
1	Record	Date	Month	Payee	Category	Master Category	Sub Category	Memo	Outflow	Inflow	Running Balance
2	1	1/1/2011	January 2011	Barnes & Noble	Recreation & His	Recreation	His		$100.00		-$100.00
3	2	2/25/2011	February 2011	Gas	Transportation & Gas	Transportation	Gas		$25.00		-$125.00
4	3	3/25/2011	March 2011	Rouse's	Food & Groceries	Food	Groceries		$130.00		-$255.00
Check Book

Excel 2007

Cell Formulas
Range	Formula
C2	=IF(B2=0,NA(),(TEXT(B2,"mmmm yyyy")))
C3	=IF(B3=0,NA(),(TEXT(B3,"mmmm yyyy")))
C4	=IF(B4=0,NA(),(TEXT(B4,"mmmm yyyy")))
E2	=IF(F2=0,NA(),(F2&" & "&G2))
E3	=IF(F3=0,NA(),(F3&" & "&G3))
E4	=IF(F4=0,NA(),(F4&" & "&G4))
K2	=SUM('Check Book'!$I2:$J2)
K3	=IF(I3=0,IF(J3=0,NA(),SUM(I3:J3,K2)),SUM(I3:J3,K2))
K4	=IF(I4=0,IF(J4=0,NA(),SUM(I4:J4,K3)),SUM(I4:J4,K3))

Excel Workbook
	A	B	C	D	E	F	G	H
1	January 2011
2	Record	Date	Month	Payee	Category	Master Category	Sub Category	Memo
3	#N/A
4
5
6
7
8
9
Months

Excel 2007

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter these manually yourself

 

[barry houdini]

I think you should have IFERROR at the start of that formula rather than IF......but if you just get a blank that probably means the dates don't match. Is A1 an actual date - it needs to be text......

 

[TheTao2]

A1 is text and changing it to IFERROR just changes the error to a blank.

 

[barry houdini]

OK, but IFERROR must be the preferred version to give you blanks when matches run out. The #N/A error might be returned because you have formulas in 'Check Book'!C$2:$C$500 that might return #N/A, that's not a good idea because your array formula will fail if any of those do actually show #N/A error.

Best to change 'Check Book'!C$2 to a formula that returns a blank.....or even text, e.g. either

=IF(B2=0,"",TEXT(B2,"mmmm yyyy"))

or

=IF(B2=0,"N/A",TEXT(B2,"mmmm yyyy"))

 

[TheTao2]

That worked great. I did not know that about array formula. I was pulling my hair out trying to get it to work.

Thank you.