0

Say you have one spreadsheet like this:

A B C D E F
Timestamp Name P1 P2 P3 P4
10/17/2021 16:28:47 Asha 1.2 3 .5 3.1
10/17/2021 23:01:38 Wei 0 0 0 0

Now, in another spreadsheet, you have something like:

A B
Name Total
Reza x
Wei x
Asha x

To calculate x for each person in the second spreadsheet, I can put a formula like this in column B:

=if( $A2<>"",
    sum(
        query(
            importrange(remote_spreadsheet_id, "Sheet1!B:F"),
            "select Col2,Col3,Col4,Col5 where Col1 = """ & $A2 & """ limit 1",
            1)
        ),
    "")

...and I get this:

A B
Name Total
Reza 0
Wei 0
Asha 7.8

...but wait! There's a problem!

Reza isn't yet in the first spreadsheet, and yet, from the output, you can't tell -- both Reza and Wei show up as 0, when I'd prefer Reza (for whom we don't yet have data) to show up as a blank ("").

How can I do this without wrapping a duplicate query in an if statement? I've tried playing with iferror and can't seem to get it right. :(

EDIT: See below for sample screenshot from my spreadsheet, where each of the 9 columns containing floating-point numbers & white backgrounds are auto-populated from different remote spreadsheets via importrange.

sample screenshot

2

Here is my suggestion:

1.) Open a new sheet in your destination spreadsheet. Name it "Import" and place the following IMPORTRANGE formula alone in cell A1 of this sheet: =IMPORTRANGE(remote_spreadsheet_id, "Sheet1!B:F"). You can Hide this sheet if you like.

2.) Move to Column B of your results sheet and delete everything in the range B2:B.

3.) Place this formula in B2:

=ArrayFormula(IF(A2:A="",,IFERROR(VLOOKUP(A2:A,FILTER({Import!A2:A,MMULT(IF(ISNUMBER(Import!B2:E),Import!B2:E,0),SEQUENCE(COLUMNS(Import!B:E),1,1,0))},Import!A2:A<>""),2,FALSE))))

This one formula should produce results for all names in Column A.

Understand that I've written this formula "by eye" and without the ability to test it on your actual data. Report back whether or not it is working as expected, and if it is, I'll then explain the basics of how it works.

8
  • Hi Erik! Thanks so much for taking the time to cook up a potential answer! Each row (each person) has 9 columns that each sum up results from a separate remote spreadsheet (via a sum(query(importrange(..))) formula). I really don't like the idea of having 9 hidden helper sheets just for the purpose of avoiding display of 0...
    – rsaw
    Oct 19 at 3:25
  • ... but if no simpler ideas show up in the next couple days, I'll try out your suggestion.
    – rsaw
    Oct 19 at 3:31
  • My proposed solution could only be based on what you've shown in your post. We can't see your entire setup, so anything beyond what is in your post would be impossible to guess. Are you saying that you are employing nine separate IMPORTRANGE formulas pulling from nine separate spreadsheets with unique IDs—one for each of nine people—and trying to compile that in one remote sheet?
    – Erik Tyler
    Oct 19 at 3:32
  • It also seems that you are saying you have not tried my solution exactly as prescribed: including one "Import" sheet with one formula in cell A1; and one results sheet with names in A2:A and one formula in B2. Is there a reason you don't want to try that simple solution now?
    – Erik Tyler
    Oct 19 at 3:37
  • "Are you saying that you are employing nine separate IMPORTRANGE formulas pulling from nine separate spreadsheets with unique IDs..." Yes, that's correct. There are about 100 people (so 100 rows). Each row has 9 columns, each with their own importrange formula, pulling from one of 9 separate spreadsheets. So yes, your approach could still work; I just kinda hate the idea of having 9 hidden sheets.
    – rsaw
    Oct 19 at 3:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.