0
=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.

1

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

  • Please remember that as per site guidelines when an answer addresses your question, you should accept it so others can benefit as well. – marikamitsos Feb 2 at 2:39
  • Dear Your formula is correct but when the dates change then it gives 0 value. If i select 01/01/2020 on both sides then answer is 0 – Shoaib Ali Feb 4 at 18:19
  • Formula corrected – marikamitsos Feb 5 at 8:55
  • Have you tried the new formula? Did it work for you? If not we should try finding out why. If yes, as per site guidelines when an answer addresses your question, you should accept it so others can benefit as well. – marikamitsos Feb 5 at 12:23

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.