1

I try to get some data from tab A to tab B using the query function. This all works fine untill I include some data of tab B into the object in the query function.

The thing is, I want to create a scoring sheet that calculates the constructor points based of the first sheet with the driver standings. So tab A looks something like this:

  1. Peter Wright - Team Fast Racers - 85
  2. Cameron Bright - Team Quickies - 60
  3. Marc Feiner - Team Fast Racers - 20

In this example the number correspond to a total amount of points on the right column.

The second tab needing to calculate the constructor points, looks simply like this:

  1. Team Fast Racers 105
  2. Team Quickies 60

Don't look at the amounts. It is just an example.

Now I want to use the query code where I can ask Google Sheets to grab the constructor name from tab B (i.e. Team Fast Racers) and look it up in tab A to get the score from both drivers and sum it up to get the total. This is the syntax now:

=QUERY({'Driver Standings'!$A$4:$T$32; 'Constructor Standings'!A1:C16}; "SELECT T WHERE C = 'B2'", 1)

The problem is that it does not recognize B2 to get the 'Team Fast Racers' name and search for it in tab A. How can I fix this problem.

Again, 2 drivers are in a team and those scores need to be summed up and displayed in the second tab to get a clean constructor championship standings. How can I get the data from tab A based on searching for a string out of tab B column B.

And if that is all done, I would also like the constructor standings (tab B) to be sorted based on amount of points, without losing the structure of the formulas.

player0
  • 69,261
  • 8
  • 33
  • 67
Danny Hoekstra
  • 41
  • 7

1 Answers1

1

first of all, all ranges in { } needs to be of the same size (at least in one dimension). if you are stacking ranges one under another then amount of columns needs to be the same:

{Sheet1!A:C; Sheet2!A:C}

then when you have a "constructed range" as above, you need to use Col references in QUERY statement

so instead of

"select A,C"

it needs to be

"select Col1,Col3"

then if B2 is a numeric number it will be

"where Col1 = "&B2

but if it is a plain text it will be:

"where Col1 = '"&B2&"'"

UPDATE:

tho all you need is:

=QUERY({'Driver Standings'!C4:C\'Driver Standings'!T4:T}; 
 "select Col1,sum(Col2)
  where Col1 is not null 
  group by Col1
  order by sum(Col2) desc 
  label sum(Col2)''")
Community
  • 1
  • 1
player0
  • 69,261
  • 8
  • 33
  • 67