1

I would like to know how to sum a column based on different criteria in a group by in google sheets query function. Here's the data And Here's what I want

Currently, I have a separate query for each summed column, and I need to copy down the row whenever the customer changes and the service providers change. Is there some way to put this into one formula so I don't need to always be copying and pasting? If one could put a query function into an array formula, that would be useful, but sadly, that is not possible. Group by allows summing of 1 column, so if I could just have multiple sums, that would be very useful. Does anyone know the best way to do this? Thanks,

Improve this question
2
  • 1
    Are you looking for a formula only solution? By the other hand, while including links to external content might be helpful, on this site, questions should be self contained, meaning, that you should include a sample data input and ouput directly on the question body. Also you should show what you tried and a brief description of your search/research efforts as is suggested in How to Ask.
    – Rubén ♦
    Nov 21, 2020 at 3:54
  • Yes, I would like a formula solution. I have tried different group by options, but can only get one column result. I'll try and update the question with the table, although the sheet I link to has the formulas I'm trying already for each column, and I'm not sure how to show those in the question, since there are several formulas.
    – Brandon Keith Biggs
    Nov 22, 2020 at 5:45

1 Answer 1

Reset to default
3

Brandon, I have added three sheets to your sample spreadsheet.

"Erik Notes" has notes (as found below).

"Erik Help" holds four formulas: in A2, B2, C2 and D2. Each "runs" its own column.

In A2:

=ArrayFormula({"Name";SORT(UNIQUE(FILTER(data!D2:D,data!E2:E=A1)))})

In B2:

=ArrayFormula({"Direct";IF(A3:A="",,IFERROR(VLOOKUP(A3:A,QUERY({data!C:F},"Select Col2, SUM(Col4) Where Col1 Contains 'Direct' AND Col3 = '"&A1&"' Group By Col2"),2,FALSE)))})

In C2:

=ArrayFormula({"Consult";IF(A3:A="",,IFERROR(VLOOKUP(A3:A,QUERY({data!C:F},"Select Col2, SUM(Col4) Where Col1 Contains 'Consult' AND Col3 = '"&A1&"' Group By Col2"),2,FALSE)))})

In D2:

=ArrayFormula({"Number of Contracts";IF(A3:A="",,IFERROR(VLOOKUP(A3:A,QUERY(UNIQUE({data!D2:E,data!B2:B}),"Select Col1, COUNT(Col3) Where Col2 = '"&A1&"' Group By Col1"),2,FALSE)))})

The A2 formula creates the header "Names" followed by an alphabetized list of all names from the "data" sheet.

The formulas in B2 and C2 are the same in structure, differing only in the header names and what is being searched for (i.e., "Direct" or "Consult"). Every non-blank entry in A2:A (i.e., each name) is run through a VLOOKUP of a QUERY made up of only names, amounts and services from "data" where the service type contains "Direct" (or "Consult" for the C2 version) and the customer matches whatever is chosen in cell A1. Since the "Group By" is based on name only, there will only be one matching result (or none) per person.

The D2 formula creates the header followed by a similar VLOOKUP of a QUERY. This QUERY finds only UNIQUE sets of data!D2:E followed by the names. Again, the elements of the QUERY have a "Group By" based on the name, so there will be one COUNT (or none) of unique entries per name for the VLOOKUP to find.

"Erik Help 2" combines all of the above formulas into one mega-formula, in A2, if you prefer that. This one formula runs everything in A2:D:

=ArrayFormula({"Name","Direct","Consult","Number of Contracts";SORT(UNIQUE(FILTER(data!D2:D,data!E2:E=A1))),IFERROR(VLOOKUP(SORT(UNIQUE(FILTER(data!D2:D,data!E2:E=A1))),QUERY({data!C:F},"Select Col2, SUM(Col4) Where Col1 Contains 'Direct' AND Col3 = '"&A1&"' Group By Col2"),2,FALSE)),IFERROR(VLOOKUP(SORT(UNIQUE(FILTER(data!D2:D,data!E2:E=A1))),QUERY({data!C:F},"Select Col2, SUM(Col4) Where Col1 Contains 'Consult' AND Col3 = '"&A1&"' Group By Col2"),2,FALSE)),IFERROR(VLOOKUP(SORT(UNIQUE(FILTER(data!D2:D,data!E2:E=A1))),QUERY(UNIQUE({data!D2:E,data!B2:B}),"Select Col1, COUNT(Col3) Where Col2 = '"&A1&"' Group By Col1"),2,FALSE))})

However, you may find the separate formulas easier to understand and edit if the need arises.

Improve this answer
2
  • Thank you! I never thought of using a query in a vlookup like that before. This is exactly what I was looking for.
    – Brandon Keith Biggs
    Nov 23, 2020 at 17:25
  • As I say often, anyone who knows anything about anything learned it the same basic way: from scratch and one piece at a time. Glad to have been able to offer one more tool for the growing toolbox.
    – Erik Tyler
    Nov 23, 2020 at 18:22

Your Answer

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

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