Attached BUDGET sheet contains 100 records covering five Cost Centres for four Departments over Jan - May 18
"P & L" sheet should work as follows:
1. From the DropList in E5, User selects either "All" or an individual Department.
2. From the DropList in E6, User selects a month between Jan - May 18
Following formulae should then show the budget for each Cost Centre against those two criteria:
Monthly formulae:
"=IF($E$5="All",SUMPRODUCT(BUDGET!$E$2:$E$101*(BUDGET!$A$2:$A$101=$B9)*(BUDGET!$D$2:$D$101=$E$6)),SUMPRODUCT(BUDGET!$E$2:$E$101*(BUDGET!$A$2:$A$101=$B9)*(BUDGET!$G$2:$G$101=$E$5)*(BUDGET!$D$2:$D$101=$E$6)))"
(If E5 says "All" then find the cost in Col E where the Cost Centre in Col A matches B9 and the Month in Col D matches E6.
But if E5 is a "Department", then find the cost in Col E where the Cost Centre in Col A matches B9, the Month in Col D matches E6, AND the Department in Col G matches E5).
YTD Formulae:
=IF($E$5="All",SUMPRODUCT((BUDGET!$D$2:$D$101>=DATE(YEAR($E$6),1,1))*(BUDGET!$D$2:$D$101<=DATE(YEAR($E$6),MONTH($E$6),1))*(BUDGET!$A$2:$A$101=$B9),BUDGET!$E$2:$E$101),SUMPRODUCT((BUDGET!$D$2:$D$101>=DATE(YEAR($E$6),1,1))*(BUDGET!$D$2:$D$101<=DATE(YEAR($E$6),MONTH($E$6),1))*(BUDGET!$G$2:$G$101<=D$5)*(BUDGET!$A$2:$A$101=$B9),BUDGET!$E$2:$E$101))
(If E5 ="All", find the cost in Col E where the Cost Centre in Col A matches B9 and the Month in Col D is between 1 Jan and the month in E6.
But if E5 is a "Department", then find the cost in Col E where the Cost Centre in Col A matches B9, the Month in Col D is between 1 Jan and the month in E6, AND the Department in Col G matches E5).
PROBLEM:
Formula works if E5 ="All" and Month ="Jan 18", Both Month and YTD calculate correctly.
But if you choose a different month, Month's Budget is blank, but the YTD calculates
And if E5=a Department and Month= Jan , Col G shows the Month's Budget but the YTD budget in Col K is blank
And if E5=a Department and Month= Feb to May, nothing calculates
Hope someone can see the flaw in the formulae logic, and all corrections, suggestions and alternative solutions welcomed as ever.
Ochimus
Bookmarks