Hi,

I was wondering if anyone could suggest an improvement to my formula?

I know its long (sorry) but it works. I have 3000 cells populated with it at the moment, here it is:


=IF(Y$5="NONE","",IF($W10<=$V$6,IF(D$2="CHANGE IN SITUATION",((IF($M$2="Days",SUMIF(TransactionDate,"=" & $X10,TransAmount),IF($M$2="Months",SUMIFS(TransAmount,TransactionDate,"<=" & EOMONTH($X10,0), TransactionDate, ">=" & (EOMONTH($X10,-1)+1)),IF($M$2="Years",SUMIFS(TransAmount,Year_Transaction,"=" & $X10)))))),IF(D$2="OVERALL SITUATION",(('Account Expenditure'!$L$23)+IF($M$2="Days",SUMIF(TransactionDate,"<=" & $X10,TransAmount),IF($M$2="Months",SUMIFS(TransAmount,TransactionDate,"<="&(EOMONTH($X10,0))),IF($M$2="Years",SUMIFS(TransAmount,Year_Transaction,"<=" & $X10))))),IF($F$3="Balance",(IF(D$2=$T$55,'Account Expenditure'!$L$8,IF(D$2=$T$56,'Account Expenditure'!$L$9,IF(D$2=$T$57,'Account Expenditure'!$L$10,IF(D$2=$T$58,'Account Expenditure'!$L$11,IF(D$2=$T$59,'Account Expenditure'!$L$12,IF(D$2=$T$60,'Account Expenditure'!$L$13,IF(D$2=$T$61,'Account Expenditure'!$L$14,IF(D$2=$T$62,'Account Expenditure'!$L$15,IF(D$2=$T$63,'Account Expenditure'!$L$16,IF(D$2=$T$64,'Account Expenditure'!$L$17,IF(D$2=$T$65,'Account Expenditure'!$L$18,IF(D$2=$T$66,'Account Expenditure'!$L$19,IF(D$2=$T$67,'Account Expenditure'!$L$20,IF(D$2=$T$68,'Account Expenditure'!$L$21,IF(D$2=$T$69,'Account Expenditure'!$L$22)))))))))))))))+IF($M$2="Days",SUMIFS(TransAmount,TransactionDate,"<=" & $X10,TransAccount,"=" & D$2),IF($M$2="Months",SUMIFS(TransAmount,TransactionDate,"<=" & $X11-1,TransAccount,"=" & D$2),IF($M$2="Years",SUMIFS(TransAmount,Year_Transaction,"<=" & $X10,TransAccount,"=" & D$2))))),IF($D$3="Catagory",IF($M$2="Days",SUMIFS(TransAmount,TransactionDate,"="&($X10),TransType,"<>"& IF($E$3="DD / SO","Manual",IF($E$3="Manual","DD / SO","All")),TransCatagory,"="&D$2,TransAmount,IF($F$3="Expenditure","<"&0,IF($F$3="Income",">"&0,IF($F$3="Cashflow","<>"&0)))),IF($M$2="Months",SUMIFS(TransAmount,TransactionDate,"<="&(EOMONTH($X10,0)),TransactionDate,">"&(EOMONTH($X10,-1)),TransType,"<>"& IF($E$3="DD / SO","Manual",IF($E$3="Manual","DD / SO","All")),TransCatagory,"="&D$2,TransAmount,IF($F$3="Expenditure","<"&0,IF($F$3="Income",">"&0,IF($F$3="Cashflow","<>"&0)))),IF($M$2="Years",SUMIFS(TransAmount,Year_Transaction,"=" & $X10,TransType,"<>"& IF($E$3="DD / SO","Manual",IF($E$3="Manual","DD / SO","All")),TransCatagory,"="&D$2,TransAmount,IF($F$3="Expenditure","<"&0,IF($F$3="Income",">"&0,IF($F$3="Cashflow","<>"&0))))))),IF($M$2="Days",SUMIFS(TransAmount,TransactionDate,"="&($X10),TransType,"<>"& IF($E$3="DD / SO","Manual",IF($E$3="Manual","DD / SO","All")),TransAccount,"="&D$2,TransAmount,IF($F$3="Expenditure","<"&0,IF($F$3="Income",">="&0,IF($F$3="Cashflow","<>"&0)))),IF($M$2="Months",SUMIFS(TransAmount,TransactionDate,"<="&(EOMONTH($X10,0)),TransactionDate,">"&(EOMONTH($X10,-1)),TransType,"<>"& IF($E$3="DD / SO","Manual",IF($E$3="Manual","DD / SO","All")),TransAccount,"="&D$2,TransAmount,IF($F$3="Expenditure","<"&0,IF($F$3="Income",">"&0,IF($F$3="Cashflow","<>"&0)))),IF($M$2="Years",SUMIFS(TransAmount,Year_Transaction,"=" & $X10,TransType,"<>"& IF($E$3="DD / SO","Manual",IF($E$3="Manual","DD / SO","All")),TransAccount,"="&D$2,TransAmount,IF($F$3="Expenditure","<"&0,IF($F$3="Income",">"&0,IF($F$3="Cashflow","<>"&0))))))))))),""))



Thank you in advance...