Search code examples
google-sheetsgoogle-sheets-formulagoogle-sheets-queryimportrange

Combination of CountIf (several conditions) & Importrange

I have two tables in two different Google Sheets documents. The first table has a table with the data, the second table is where I write a formula.

I cannot count the number of rows in which in two different columns in cells two different requirements are met. By two different requirements, I mean two different letters.

Here is an example of a table:

I need to count the rows in which "S" is in column A and "N" is in column B

A link to a table: https://docs.google.com/spreadsheets/d/1Gx9Oa1d_jtQnxD3Rory-WBeiNw0_3gCqabFEt-VT8yA/edit?usp=sharing

I've tried different combinations:

=COUNTA(QUERY(IMPORTRANGE("1Gx9Oa1d_jtQnxD3Rory-WBeiNw0_3gCqabFEt-VT8y";table!A:B);"Select Column1 Where Column1 = 'S' and Column2 = 'Y'"))

=COUNTIFS(IMPORTRANGE("1Gx9Oa1d_jtQnxD3Rory-WBeiNw0_3gCqabFEt-VT8y";table!A:A);"S";IMPORTRANGE("1Gx9Oa1d_jtQnxD3Rory-WBeiNw0_3gCqabFEt-VT8y";table!B:B);"Y")

Also tried combinations with if and filter.

Solution

  • you need to do it like this:

    =COUNTA(IFERROR(QUERY(A:B; "select A where A='S' and B='N'"; 0)))

    0

    and then IMPORTRANGE:

    =COUNTA(IFERROR(QUERY(
 IMPORTRANGE("1Gx9Oa1d_jtQnxD3Rory-WBeiNw0_3gCqabFEt-VT8yA"; "table!A:B"); 
 "select Col1 where Col1='S' and Col2='N'"; 0)))

    0