Google Spreadsheets: Using QUERY & IMPORTRANGE, SUM Two Separate Ranges On Remote Sheet Based On Condition

Question

I'm having difficulty summing two separate ranges based on a condition -- essentially a SUMIF of data from a remote sheet.

Currently, I'm using a formula that combines both ranges. This works, but will become problematic when I attempt to SUM ranges that are not vertically aligned.

Formula I'm using now (which works): =SUM(QUERY(IMPORTRANGE($B$34,TEXT($B2,"m/d/yyyy")&"!$F$8:$I$19"),"SELECT Col4 where Col1='"&$C$34&"'",0))

Formula Key:
- $B$34 = Reference to Google Spreadsheet
- $B2 = Reference to Sheet Name
- $C$34 = Reference to Condition

I'd like to split up the combined range ($F$8:$I$19) into two separate ranges ($F$8:$I$12 + $F$15:$I$19). I've made several unsuccessful attempts, including this one:

=SUM(QUERY(IMPORTRANGE($B$34,TEXT($B2,"m/d/yyyy")&"!$F$8:$I$12"),"SELECT Col4 where Col1='"&$C$34&"'",0))+SUM(QUERY(IMPORTRANGE($B$34,TEXT($B2,"m/d/yyyy")&"!$F$15:$I$19"),"SELECT Col4 where Col1='"&$C$34&"'",0))

Guidance would be very much appreciated.

You can view the test sheets I'm using here:

Formula Test:
https://docs.google.com/spreadsheets/d/1x6blsRLlfYXvTlO_cf71woY6yC4K2HxjKyMKZnvbUI8/edit#gid=1027872696

Target Data:
https://docs.google.com/spreadsheets/d/1tZBhtoZWcE-BKdwNy0cQ4H4_ktGVpDHVG2rarZlr4I4/edit#gid=0

Answer 1

This approach seems to work:
=SUM(QUERY({IMPORTRANGE($B$34,TEXT($B2,"m/d/yyyy")&"!$F$8:$I$12");IMPORTRANGE($B$34,TEXT($B2,"m/d/yyyy")&"!$F$15:$I$19")},"SELECT Col4 where Col1='"&$C$34&"'",0))

Hat tip: Google Sheets - QUERY from Another Sheet, IMPORTRANGE, Use Multiple Tabs, Subquery Examples Tutorial