Array formula causing #VALUE error

Question

Have, what I think is, a correct formula that is only partially working.

=IF(Control_Category<>"",IF(Transaction_Type="Income",SUMPRODUCT((September!Budgeted_Income)*(September!Category=$A13)*(September!Budget_Date&"<="&TODAY())),SUMPRODUCT((September!Budgeted_Expense)*(September!Split_Receipt="")*(September!Category=$A13))),IF(INDIRECT(ADDRESS(ROW()-1,1))<>"",SUM(INDIRECT(ADDRESS(2,COLUMN())&":"&ADDRESS(ROW()-1,COLUMN()))),0))

Control_Category  and Transaction_Type are on the current sheet as 100 x 1 arrays.

September!Budgeted_Income is on a second sheet as a 300 x 1 array

September!Category is on another sheet as a 300 x 1 array

September!Budget_Date is on another sheet as a 300 x 1 array

Today() is, of course, today's date

As coded the formula, when Control_Category <>"" and Control_Category = "Income" I get a return of #ERROR instead of the Month-to-Date budget amount for income. When I remove the highlighted the formula works but produces the budget for the entire month instead of Month-to-Date.

The second half of the formula, which deals with expenses and does not test the date works fine.

Need some assistance please,

TheOldPuterMan

Answer 1

TOPM,

Rewrite youre formule like this:

=IF(Control_Category<>"",IF(Transaction_Type="Income",SUMPRODUCT((September!Budgeted_Income)*(September!Category=$A13)*(September!Budget_Date<=TODAY())),SUMPRODUCT((September!Budgeted_Expense)*(September!Split_Receipt="")*(September!Category=$A13))),IF(INDIRECT(ADDRESS(ROW()-1,1))<>"",SUM(INDIRECT(ADDRESS(2,COLUMN())&":"&ADDRESS(ROW()-1,COLUMN()))),0))

and all will be fine.

So the quotes in & "<=" & where too much.

Jan

Answer 2

Thank you!! Didn't even consider that but should have. Worked like a charm!

TheOldPuterMan