Google-sheets – IMPORTRANGE QUERY formula filtered by dates range and values from cell

formulasgoogle sheetsgoogle-sheets-queryimportrange

=COUNTA(IFERROR(QUERY(IMPORTRANGE("Sheet1","QC sheet!A792:B10000"), "Select Col2, Col4 where Col2 contains '"&B$1&"' and Col4 contains '"&C1&"' and date "&TEXT(G1,"'YYY-MM-DD'")&" <=Col1 and date "&TEXT(H1,"'YYY-MM-DD'")&" >=Col1" ,0)))

Using above formula to count data with 2 containing conditions along with start and end date does not work.

https://docs.google.com/spreadsheets/d/1hMoJAtBeXKGMFcghMDZaIcBjfJAD9mF35YVXbXYZ5Iw/edit#gid=597186154

EDIT 1:
https://drive.google.com/file/d/1DTMFViXxBn-ezPJ2Njjbor-4udoRyc8M/view?usp=sharing
Please update the formula sheet have been attached.

Edit 2: 

=COUNTIFS(D3:D50,A1,E3:E50,B1,C3:C50,">0",C3:C50,F1,C3:C50,G1)

This formula is working but how we can calculate same with query formula actually i have to get data by importrange.

Best Answer

Basically there are two mistakes in your query formula.

  1. IMPORTRANGE("Sheet1","QC sheet!A792:B10000") can not return anything. The reason being that you need to replace the Sheet part of the formula with the sheet's ID that you want to import from. (More info about IMPORTRANGE)
  2. You mention Col4 when you import just 2 columns A and B from sheet!A792:B10000.
    (More info about QUERY)

I do not know about the rest of it (no obvious mistakes) since I cannot access your sheet.
(Please read how to share a Google spreadsheet so as you can be easier helped.)

Correct the formula, see if it works for you and let us know.

EDIT (following the fixed sheet)

=COUNTA(IFERROR(QUERY({IMPORTRANGE("191k0EcOCZKzorxG5jb-H6vIM6GKGzq2r8638q2Yc7fA","C3:F100")}, 
                           "Select Col2 where Col2 contains '"&A$1&"' 
                                          and Col3 contains '"&B1&"' 
                                          and Col1>= date"&TEXT(F1,"'YYY-MM-DD'")&" 
                                          and Col1<= date"&TEXT(G1,"'YYY-MM-DD'")&"",0)))

enter image description here

Related Topic