CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formulaarray-formulasgs-vlookupgoogle-query-language
Fabrice
Fabrice

Reputation: 535

Google SpreadSheet Query - Merge queries results into one

Let's take this data in a Google sheet:

| Product | Green | Red | Date      |
| A       | 1     | 0   | 1/1/2020  |
| A       | 1     | 0   | 2/1/2020  |
| B       | 0     | 1   | 2/25/2020 |
| C       | 1     | 0   | 2/28/2020 |
| A       | 0     | 1   | 3/1/2020  |

My goal would be to display the sum of Green / Red for each product:

I created this Google Query to get the results for all the year:

=QUERY(DATA!A:D,"select A, sum(B), sum(C) where D >= date '2020-01-01' and D <= date '2020-12-31' group by A")

I get this result:

| Product | sum Green | sum Red |
| A       | 2         | 1       |
| B       | 0         | 1       |
| C       | 1         | 0       |

And this query for the given month (I simplified the query, but I have a Settings sheet to specify the month to query):

=QUERY(DATA!A:D,"select A, sum(B), sum(C) where D >= date '2020-01-01' and D <= date '2020-01-31' group by A")

And get this result:

| Product | sum Green | sum Red |
| A       | 1         | 0       |

Now I'm stuck in joining the two results into one, like this:

| Product | Year sum Green | Year sum Red | Jan sum Green | Jan sum Red |
| A       | 2              | 1            | 1             | 0           |
| B       | 0              | 1            |               |             |       
| C       | 1              | 0            |               |             |

How can I achieve this ?

Thanks a lot for your help!

Upvotes: 1

Views: 365

Answers (1)

player0
player0

Reputation: 1

try:

=ARRAYFORMULA(IFNA(VLOOKUP(F2:F, QUERY(DATA!A:D, 
 "select A,sum(B),sum(C) 
  where month(D)+1 = 1 
  group by A 
  label sum(B)'Jan sum Green',sum(C)'Jan sum Red'"), {2,3}, 0)))

0

0

Upvotes: 3

Related Questions