1

This is likely an easy feat for those how to work Sheets but I have no idea how to do it myself. I am looking at a quarterly report of airline complaints, where the source publishes one csv per quarter. These quarterly reports have the airline names in column A with the type of complaints in the subsequent columns, with a count for each type of complaint received per airline.

Airline Complaint type 1 Complaint type 2
Airline 1 23------------ 12----------------
Airline 2 21------------ 7-----------------

There are specific types of complaints that interest me and I would like to add up those quarterly reports to have the count of complaints for the year. The only issue is that the amount of airlines doesn't match between quarterly reports, so Q1 might show airlines 1-50 but others might be missing airlines 34-41, or might even have airlines 50-55 that don't show up on the Q1 sheet. I am after a function where I can ask to find the count of, say, complaint type 1, for each airline, in order to add up to a yearly total (in this case I have a tab for each quarter). Can anyone help with this?

1 Answer 1

1

You can try with QUERY, stacking all the sheets one over the other. For example for Complaint type 2:

=QUERY(VSTACK(Q1!A:E,Q2!A2:E,Q3!A2:E,Q4!A2:E),"SELECT Col1, SUM(Col3) where Col1 is not null group by Col1")

I named your sheets Q1, Q2 and so on. Do adapt this. SUM(Col3) is the sum of Complaint 2 because it's in the 3rd column of the range

3
  • YES! That works perfectly, thanks. I've just modified the SUM function in relation to which I want to show and it does exactly what I want to do. Thanks again!
    – bernicla
    Jul 6, 2023 at 13:33
  • I'm glad it was useful!! I forgot to tell you that you can have more columns for different types of complaints: =QUERY(......,"SELECT Col1, SUM(Col3),SUM(Col5).......") , for example. Good luck!
    – Martín
    Jul 6, 2023 at 13:44
  • Thanks a lot, this works too! I tried it with the AVG formula to get an average of a specific value but it doesn't map on to the individual tabs in the sheet - do you know which formula would help get an accurate average? Let's say if there if I wanted an average amount of types of complaints per airline? Thanks!
    – bernicla
    Jul 26, 2023 at 14:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.