Google-sheets – Trying to make a column of “select count” in reference to another column

formulasgoogle sheetsgoogle-sheets-query

I am pulling data from multiple sheets in my workbook to create an array. In the first part of the array I pull data straight from a sheet like a user name # of points and rank. Now for the rest of the data I need to count the # of instance a particular user pop with certain value in other sheets.
In one query I am trying to create a column that counts the number of times the value '7' exists for each user.
In sheet1 the user is in column A and in the active sheet it is in B.

Now if I make the query 

 =QUERY(sheet1!A:F,"select count(D) where D=7 and A = '"B2"'",0)

I get the right answer but in 2 rows. One with the title and another one with the value. So I can't repeat the same query for each row.

When I try this query

 =QUERY(sheet1!A:F,"select count(D) where D=7 and A = '"B"'",0)

It doesn't recognize B as a column and gives an error.

Best Answer

On the first formula the concatenator operators & are missing. The correct syntax is

=QUERY(sheet1!A:F,"select count(D) where D=7 and A = '"&B2&"'",0)

Regarding the second formula B isn't a valid reference.

To count the number of times the value 7 exists for each user you could

  • use GROUP BY if you want that the row header be calculated automatically

    =QUERY(sheet1!A:F,"select A, count(D) where D=7 GROUP BY A",0)

  • use a bit convoluted formula to take the user names from column B to create a text value having a condition including the user names (values of column B)

     JOIN(" OR ", "A = '" & FILTER(B:B,LEN(B:B)>0) & "'"))

    The final formula will look like this

    =ARRAYFORMULA(
   QUERY(
     sheet1!A:F,
     "select count(D) where D=7 and " & 
        JOIN(" OR ", "A = '" & FILTER(B:B,LEN(B:B)>0) & "'")),
     0
   )
)
Related Topic