Google Sheet : Consolidate Data Using ImportRange Based in Criteria

You should consolidate the data in the source file first, and then do a simple importrange of the consolidated data.

usually you would stack the ranges in the source sheet with a query, something like this:

Please Login or Register  to view this content.

BUT... you have decided to put a total under the data, so you wont be able to use open ranges... this isnt a big problem, you can just limit the ranges so you wont need the QUERY, like this:

Please Login or Register  to view this content.

I hope that helps.

In your source file i added new columns to identify the data types, then created the consolidated data with:

Please Login or Register  to view this content.

then in the destination file i imported with:

Please Login or Register  to view this content.

then finally a query to get the table of totals, with:

Please Login or Register  to view this content.