Sumproduct, Indirect, Address over multiple sheets and varying row lengths

Dear all,

I have a summary sheet which I'd like to refer to the input sheets (of which there are 3 departments) and SUM the fruits by month and by department.

Each department has to be in a table format, but they will all vary in length each month.

I've tried to use the formula found in C9 on the summary sheet. This uses SUMPRODUCT, INDIRECT and ADDRESS. I'm trying to create ranges of cells based on the first and last rows of each department table and refer to these through INDIRECT. However, I keep coming back with #REF!.

Please is someone able to point out to me a way to complete this formula? Or if there is in fact a better way to solve this problem of summing across multiple sheet with varying row lengths?

All help is appreciated.

ThanksNew Fruits.xlsx

Dear hemesh,

Thank your for your reply. I've used all three of your solutions and they work perfectly. Many thanks for your help.

Oeldere I shall try your solution once I have finished my report.

Kind regards.