How can I combine multiple IMPORTRANGE with QUERY to SUM data from another spreadsheet?

Question

I've had to move a set of data outside of a workbook because the workbook was starting to hit cell limitations. Now I want to send some of the data from the new workbook back into the original workbook to add it to data still in that original workbook.
After lots of trial and error, I finally landed on the following:
Using QUERY to aggregate the data
Using two IMPORTRANGE functions to split the data from the workbook up, after getting an error that using one IMPORTRANGE was too large
Using the query "select sum(Col13) where Col1 like '%"&A2&"%' label sum(Col13) ''" to sum my costs column whenever there are matching unique identifiers (CID) in column A of the original workbook and column A (Col1) of the new workbook
Here's the full formula:
​=QUERY({importrange("https://docs.google.com/spreadsheets/d/1tEY3yR2Ch6ntZbRh7rGUFBwor6HvYpIu4vnzi8JCxz8/edit?usp=sharing","Facebook!A:L");importrange("https://docs.google.com/spreadsheets/d/1tEY3yR2Ch6ntZbRh7rGUFBwor6HvYpIu4vnzi8JCxz8/edit?usp=sharing","Facebook!M:V")},"select sum(Col13) where Col1 like '%"&A2&"%' label sum(Col13) ''",1)/(1-24%)
Unfortunately, I'm getting an error: "Unable to parse query string for Function QUERY parameter 2: NO_COLUMN: Col13"
Here's a copy of the new workbork for reference: https://docs.google.com/spreadsheets/d/1tEY3yR2Ch6ntZbRh7rGUFBwor6HvYpIu4vnzi8JCxz8/edit?usp=sharing

Gill K · Accepted Answer

Hi Richard,
I haven't finished looking into this, but the current error in your formula is that you are querying a virtual array that is only 12 columns wide, so no column 13.
You've stacked one IMPORTRANGE on top of the other, so columns A:L of your Facebook tab are on top of columns M:V.
That's probably not what you mean to have.  But putting them side by side, by using a comma instead of a semi-colon, gets back to the "Result too large" error.
I think that the real issue isn't with the columns, it is with the number of rows you are trying to import.  And also the number of cells in your current worksheet, not the source sheet.
I went to a new/empty worksheet, and got the following formula to work, importing ~50,000 rows from your source sheet.
=QUERY({importrange("1tEY3yR2Ch6ntZbRh7rGUFBwor6HvYpIu4vnzi8JCxz8",
                    "Facebook!A1:V10000");
        importrange("1tEY3yR2Ch6ntZbRh7rGUFBwor6HvYpIu4vnzi8JCxz8",
                    "Facebook!A10001:V20000");
        importrange("1tEY3yR2Ch6ntZbRh7rGUFBwor6HvYpIu4vnzi8JCxz8",
                    "Facebook!A20001:V30000");
        importrange("1tEY3yR2Ch6ntZbRh7rGUFBwor6HvYpIu4vnzi8JCxz8",
                    "Facebook!A30001:V40000");
        importrange("1tEY3yR2Ch6ntZbRh7rGUFBwor6HvYpIu4vnzi8JCxz8",
                    "Facebook!A40001:V49500")
       },
"limit 55555",0)
I think that that is all of your data, at least in the sample sheet.
Your sample sheet has a huge number of blank rows at the bottom, so the importrange functions are pulling all of that blank data as well.  I think it is just too many cells.
I've stopped the import at row 49,500.
Does that help?
The next issue is that your cost values in column M are just text that look like numbers.  So the query sum won't work.
I don't know whether you can convert the values to numbers in your original Facebook worksheet.
But we can convert them here as part of the import.
Lastly, if you are only trying to do a sum of the costs, there is no need to import all of the other columns.  If you are doing this for reporting reasons, you may want several different formulas, each of which just reports on one aspect of the data.
For example, try this formula:
=ArrayFormula(
 QUERY({      importrange("1tEY3yR2Ch6ntZbRh7rGUFBwor6HvYpIu4vnzi8JCxz8",
                          "Facebook!A1:A49500"),
        VALUE(importrange("1tEY3yR2Ch6ntZbRh7rGUFBwor6HvYpIu4vnzi8JCxz8",
                          "Facebook!M1:M49500")) },
  "select Col1,sum(Col2) group by Col1 label sum(Col2)'VALUE'",1))
This imports all the rows (up to 49500) but only columns A and columns M.  It also converts column M from text to numbers, so the query can sum them.
I don't know what criteria value you have in A2, so it reports for each unique value from column A.
See tab GK.Help for a demo.
Cheers,
Gill