Formula needed for column and row dependency

Question

Tried several times with Query formula, but failed so far.

I have a simple source that looks like this, columns of date, product and quantity.

On another sheet I have a table that has the products on the Y-axis and the years on the X-axis. Both axis I want to expand over time with the products and years available in the source and their respective sum of quantities in the grid.

Existing formulas are:

To put out the years in row 1.

=transpose(ArrayFormula(SORT(UNIQUE(YEAR(FILTER(Log!A2:A,ISNUMBER(Log!A2:A)))),0,false)))

To put out the products in column A.

=query(Products!A2:A,"select A where A is not null order by A")

What formula I have to put in B2 to find all the quantities from the source table matching their respective year of date and their product?

Answer 1

You may try:

=arrayformula(query({year(Log!A2:A),Log!C2:D},"Select Col2,sum(Col3) Where Col2<>'' group by Col2 pivot Col1")) 

• Adjust the ranges in the formulas as per your sheet