0

Given table A:

id value1 value2
1 0.33 0.25
2 0.4 0.7

and table B:

date id quantity
01/08 1 0.25
01/08 2 0.45
02/08 1 1
04/08 2 0.7

I would like to create a table which joins A and B by id and sums all value1 and value2 for all dates like this:

date value1 value2
01/08 0.25 * 0.33 + 0.4 * 0.45 0.25 * 0.25 + 0.45 * 0.7
02/08 0.33 0.25
03/08 0 0
04/08 0.7 * 0.4 0.7 * 0.7

How can I achieve this?

1

1 Answer 1

0

Use query(), like this:

=arrayformula( 
  query( 
    { 
      B!A1, A!B1:C1; 
      B!A2:A, 
      B!C2:C * iferror( vlookup(B!B2:B, A!A2:C, { 2, 3 }, false) ) 
    }, 
    "select Col1, sum(Col2), sum(Col3) 
     where Col1 is not null 
     group by Col1", 
    1 
  ) 
)

This formula only shows the dates where data actually exists. To show all dates between the earliest and latest date and insert superfluous blank data in those rows, use this:

=arrayformula( 
  iferror( 
    vlookup( 
      sequence( max(B!A2:A) - min(B!A2:A) + 1, 1, min(B!A2:A) ), 
      query( 
        { 
          B!A2:A, 
          B!C2:C * iferror( vlookup(B!B2:B, A!A2:C, { 2, 3 }, false) ) 
        }, 
        "select Col1, sum(Col2), sum(Col3) 
         where Col1 is not null 
         group by Col1", 
        0 
      ), 
      { 1, 2, 3 }, 
      false 
    ), 
    iferror( 
      sequence( max(B!A2:A) - min(B!A2:A) + 1, 1, min(B!A2:A) ) / { 1, 0, 0 } 
    ) 
  ) 
)
3
  • This is great. Thank you. Is it possible to insert missing dates? They don't appear when I try this code. Aug 14, 2021 at 10:03
  • Edited the answer to add another formula. It is unclear why you would need to show those superfluous dates with no data. Is that an XY problem? Aug 14, 2021 at 14:05
  • Well I suppose it's not a requirement. This aggregated table is visualized to me personally and I just prefer it this way. :) Thank you for your help. Aug 14, 2021 at 14:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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