Google Sheets ArrayFormula

0
votes

Have 2 tables in 2 sheets, a 1 to many relationship between sheet1 and sheet2.

Need an ArrayFormula in sheet1 (one side) that sums sheet2.B for all sheet1.A = sheet2.A

I have a SUMIF that works individually eg: sheet1.A3

Searching showed examples where the rows aligned. My case, matching records can be anywhere in the 'many' table.

Or maybe I'm understanding this wrong?

Pointers please.

EDIT1;

Sheet1: // one side of one to many relationship
    column A: key1, key2, key3, ...

Sheet2; // many side of one to many relationship
    column A: key1, key2, key1, key2, key2, key3, key1, ...
    column B : num1, num2, num3, num4, ...

In sheet1 cell B1 i need an ArrayFormula that sums sheet2.B for all matching keys ( eg: sheet1.B2 must contain sums for matchig sheet1 key "key2" from sheet2.A )

Edit2; link to a sample sheet https://docs.google.com/spreadsheets/d/1YXzOUJYTyPR3YGQgdi14tzXheSK40mkBzMGg_LJID4M/edit?usp=sharing

1
google-sheets
Sample data, please. – Max Makhrov
Updated original post to include link to a sample spreadsheet – Riyaz Mansoor

1 Answers

1
votes

I finally managed to do it using array formula - now I get auto expansion :)

=ARRAYFORMULA(IFERROR(VLOOKUP(A1:A10,QUERY(Many!A1:B,"select A, sum(B) group by A label sum(B) ''"),2,FALSE),0))