0

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.

1

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. – Mathieu Gauthier Sep 4 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 at 18:33
  • Yes I am trying to create an array. I edit my question. – Mathieu Gauthier Sep 6 at 14:14
  • @MathieuGauthier I edited my answer. – Rubén Sep 6 at 14:41

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.