# Trying to make a column of "select count" in reference to another column

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.

• Welcome. Also, please remember that as per site guidelines when an answer addresses your question, you should accept it so others can benefit as well -. (Why vote?) Sep 3 '19 at 18:21

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
)
)
``````
• The problem is that I want the value to match the user on the active sheet. If I group by "A" it will group from "sheet1" and I am not able to match it whit the one on active sheet. Sep 4 '19 at 18:29
• @MathieuGauthier Do you mean that you want to compare with an array of values? If so, please edit your question to clarify this and provide sample input data and conditions like if the data of the active sheet will be static or dynamic and if it will be dynamic how it will be changed over time.
– Rubén
Sep 4 '19 at 18:33
• Yes I am trying to create an array. I edit my question. Sep 6 '19 at 14:14
• @MathieuGauthier I edited my answer.
– Rubén
Sep 6 '19 at 14:41