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?

rockinfreakshow · Accepted Answer · 2024-03-04 07:34:23Z

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

answered Mar 4, 2024 at 7:34

rockinfreakshow

30.7k4 gold badges19 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

individual8 Over a year ago

Works nicely out of the box already. 👍 Let's say I want to do the same for months instead of years. How do I put out all 12 months, not just the existing in Log!A2:A and how do I replace the resulting integer value with its month (January, February, ...)?

rockinfreakshow Over a year ago

It would help if you could share a sample sheet with realistic-demo data and expected output(manually entered) so as for us to get a clear picture & workout a solution. You may also use the anonymous blank sheet maker provided by docs community forum incase you do not wish to share a sheet from your google account.

individual8 Over a year ago

Sure @rockinfreakshow. Here's an example: docs.google.com/spreadsheets/d/…

rockinfreakshow Over a year ago

There's no input data added within the sheet

rockinfreakshow Over a year ago

Formula added to the test sheet

|

Collectives™ on Stack Overflow

Formula needed for column and row dependency

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related