I have an IMPORTRANGE(…) that yields the following table:
| Adoption Date | Take-Home Date | Name | Dogs | Puppies | Cats | Kittens | Chickens | Chicks | … |
|---|---|---|---|---|---|---|---|---|---|
| 01/25/2023 | 01/26/2023 | Cody | 3 | 0 | 2 | 1 | 30 | 5 | … |
| 02/24/2024 | 02/29/2024 | Rob | 0 | 2 | 0 | 4 | 0 | 0 | … |
| 01/29/2024 | 01/29/2024 | Rob | 1 | 0 | 2 | 4 | 2 | 10 | … |
| 01/29/2024 | 01/29/2024 | Rob | 2 | 1 | 0 | 1 | 5 | 3 | … |
| … | … | … | … | … | … | … | … | … | … |
I would like to wrap that function in something like a QUERY() with a GROUPBY or a PIVOT() or some combination of TRANSPOSE() and SUM() to yield the following result:
| Adoption Date | Take-Home Date | Name | Animal | # Adults | # Young |
|---|---|---|---|---|---|
| 01/25/2023 | 01/26/2023 | Cody | Canine | 3 | 0 |
| 01/25/2023 | 01/26/2023 | Cody | Feline | 2 | 1 |
| 01/25/2023 | 01/26/2023 | Cody | Bird | 30 | 5 |
| 02/24/2024 | 02/29/2024 | Rob | Canine | 0 | 2 |
| 02/24/2024 | 02/29/2024 | Rob | Feline | 0 | 4 |
| 01/29/2024 | 01/29/2024 | Rob | Canine | 3 | 1 |
| 01/29/2024 | 01/29/2024 | Rob | Feline | 2 | 5 |
| 01/29/2024 | 01/29/2024 | Rob | Bird | 7 | 13 |
| … | … | … | … | … | … |
Note that there is no row for Rob’s Birds, since those values are all zero.
In essence, I need to take all unique combinations of Adoption Date, Take-Home Date, Name, and (pivoted) animal species, and aggregate the sum of adults and the sum of young. I’m sure this is possible in one formula, but can’t wrap my mind around the grouping, aggregation, and pivot as a function of a query SQL statement. Thanks for any help.