Sumif with dinamic criteria

[Adell]

Hallo, I have a budget, with sheets for every department and a consolidated sheet. On a summary sheet, I want to be able to select a department and a month and the relevant financial information will be visible. My line items (financial accounts) are in column C, I have a drop down list with all the department names in E1, month selection in E2 - this is the month's name and not date, in F2 i have a formula to calculate the column witch the month's information will be in In G2, similar formula, but to line 100 (last row of my sheets). I have been able to create a formula for the current month's values. BUT, I now need to calculate the Year to date (thus month 1 to month as selected in E2) value and then Balance remaining (this will be Total - YTD) per Department selected. The same will be for the Consolidated figures. Can you help me with the YTD figures and if a 'cleaner' formula for MTD? Thank you

 

[Adell]

Don't worry, I got it working. To calculate the YTD my formula is: =SUMPRODUCT((INDIRECT("'"&E$1&"'!"&"$c$2:$c$100")=$C7)*INDIRECT("'"&E$1&"'!"&"$f$2:"&$G$2))
and to get the Total for the year, =VLOOKUP($C7,INDIRECT("'"&$E$1&"'!"&"$c$2:x100"),16,0).

​

 

[Adell]

I do however get a #value error, when I select August, September, October, November or December, BUT only in the department's financial side, the Consolidated side has no errors. (Consolidated was a created from copying a Department sheet and amending the formulas). Has any one come across such an error before and how do I fix it? Thx

 

[Adell]

Feeling really stupid. Row 54 on Department's sheets from column M had an " ' " in it. Deleted it on all and it is working perfectly. Strange that it gave error from row 7 right through and not only in row 54.