SUMPRODUCT, SUMIFS, INDIRECT how to have a flexible lookup range across multiple worksheet

[dma1976]

Dear all,

I am able to sum costs based on sheet name criteria and lookup criteria for the enlcosed file.

However, I have had to manually enter the lookup range in the SUMPRODUCT(SUMIFS(INDIRECT formula on the summary sheet: (F1:F10 and B1:B10).

Each lookup sheet has a table with the detailed data. If this data extends beyond 10 rows, this will obviously not be pulled through to the summary sheet.

Without having to enter a large number into the formula look up range, say F1:F10000, is there a way of the SUMPRODUCT formula being able to detect the last row to sum to?

Perhaps it is possible for the lookup to be based on the criteria, and searches on the appropriate table name.

Thanks in advance.Fruits_3.xlsx

[Jacc]

Everything is already set up for a smooth solution. The tables are of the Excel Table feature kind, that will expand automatically when data is added. These tables also allow for referencing columns by the header name and hence will provide exactly what you want. I implemented this in the attached workbook.
Also, these tables are already named with the same name as the sheet so it seems like someone had a plan. Referencing directly to the table name means that you don't have to worry about the sheet name.

Your original formula makes me wonder if I'm missing something because it seems to search all the sheets. With the sample data you provided that is not neccesary.

How do you get your other columns in the summary sheet by the way?

[dma1976]

Dear Jacc,

Thank you for your solution.

I have used the SUMPRODUCT formula many times, but have never found a way of substituing the F1:F10000 part of the formula for a dynamic range of rows, and also having the F:F column move based on a criteria.

I thought that tables may provide the answer. It seems they can as long as you reference the table name (or at least call the table the same as the worksheet).

[Jacc]

Actually, in my workbook you can change the sheet names to whatever you want as they are not part of the formula, only the Table names are. The Tables, of course, have to have names corresponding to the fruits.