0

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!

Improve this question

1 Answer 1

Reset to default
0

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

Improve this answer
5
  • Woops, I started with letters then thought it would be clearer to change to numbers but didn't update the second table. I just tried your suggestion and it works perfectly! I'm going to need to spend some time learning LAMBDA. No idea how it works yet, but seems like a very powerful option. I also wasn't familiar with BYROW, so thanks for your answer!
    – jpfieber
    Nov 7, 2022 at 17:23
  • Great! I'm glad it was useful! I'm no expert, I learned about them last week. Basically Byrow and Bycol iterates an operation through each cell of a column or a row respectively. You set a range then comma and then a lambda function. That is you stablish a name for that cell as a variable (I named it "each" but you can name it "category" or whatever you want), and you set the same formula as always (SUMIF in this case) but replace that cell that iterates with the variable you chose. It has its little workarounds, but Byrow and Bycol are great tools!
    – Martín
    Nov 7, 2022 at 17:33
  • Hey, I had to leave the computer and had left some idea left to share with you. Another thing you can do if you don't need to stablish the categories first is to use a Query like this: =Query(A:B,"Select A, Sum(B) where A is not null group by A") and you can even add "sort by A" if you want it ordered alphabetically
    – Martín
    Nov 7, 2022 at 22:39
  • Thanks, I played with query a bit and it just wasn't working the way I wanted, but the byrow/lambda function worked perfectly right away so I'm gonna stick with what works! Plus, hopefully I'll start playing with lambda more and get a better understanding of how to use it, seems very powerful! Thanks again!
    – jpfieber
    Nov 8, 2022 at 2:29
  • Great! Good luck!!
    – Martín
    Nov 8, 2022 at 3:26

Your Answer

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