CODEWITHSUNDEEP
google-sheetssumgoogle-sheets-formulagoogle-sheets-querygoogle-query-language
David Kelly
David Kelly

Reputation: 21

Google Sheet Query statement

Thanks in advance for any help. So here’s my dilemma, this is an example (copy) of a Google sheet with raw data in first tab and I’m trying to extract summary data onto the second tab.

https://docs.google.com/spreadsheets/d/1IRlq-1cVaW1thzH8_c0nqNIJlLykaenJIO67RrS7kQE/edit?usp=sharing

I’ve done it via a Google sheets Pivot Table - it’s supposed to rank the people who have done the most miles in descending order, taken from the main Data tab (i.e. the highest number of miles first). The problem is that for some odd reason, when it hits someone with the same first name, it groups them and it messes up the descending order (see James McLeod then James Stringer as an example).

On the main sheet, as an alternative method, I’ve been experimenting with their QUERY function which is very powerful and should do the the job. You can see examples of these in the first results tab on the right side starting at cell H1. I have named the data from columns A:F as a range called "Results"

The query in H1 is almost right but it won’t let me add first name and role column which I need (as per the Pivot Table example). I need to include First Name, Last Name, Role and the total distance they have achieved, sorted by highest to lowest distance.

Eventually this summary data needs to be on a new tab anyway

Thank you, any help would be most appreciated!

Upvotes: 2

Views: 176

Answers (2)

user11982798
user11982798

Reputation: 1908

Without query you can use filter, unique, sort like this:

={Data!B1:D1,"Sum of distance";unique(filter(sort({Data!B2:D, sumif(Data!B2:B & Data!C2:C,"=" & Data!B2:B & Data!C2:C,Data!F2:F)},4,false,1,true),Data!B2:B <>""))}

Upvotes: 0

player0
player0

Reputation: 1

try:

=QUERY(Results, "select B,C,D,sum(F) group by B,C,D order by sum(F) desc", 1)

0

Upvotes: 1

Related Questions