Google Sheets – Sum Filter of Table 1 in each row of Table 2

google-sheets-arrayformulagoogle-sheets-arraysgoogle-sheets-filtergoogle-sheets-queryvlookup

I have table of transactions with categories (let's say 1-5) and assigned dollar values, and another table with a list of each category. I want to sum up all the matching categories from Table 1 with the result in each row of table 2.

Table 1 (list of transactions)
enter image description here

Table 2 (sum of transactions for each category)
enter image description here

The actual Table 2 I'm working with has many rows and is constantly changing, so I don't want to fill a formula in each cell (Something like =QUERY(A:B,"Select sum(B) where A = 'C' LABEL sum(B) ''") would work in that case). I'm thinking something like an ARRAYFORMULA or something with INDEX/FILTER/VLOOKUP that can fill the entire column. Help!

Best Answer

You can do it with Byrow (you can think it as an arrayformula, but it can overcome some of its limitations):

=byrow(D2:D,lambda(each,if(each="","",sumif(A2:A,each,B2:B))))

Here's a sample with the implementation and sample of these two tables:

'enter image description here

PS: I've seen in Table 1 you have numbers for categories, and in Table 2 there are letters. Is it that way?? The formula would work if the categories in both tables match

Related Topic