I have such table of payments:
account | amount
--------+-------
101 | 3
101 | 5
102 | 7
103 | 9
I named the range of this table "payments". Its first column I called "accounts". Its second column I called "amounts".
I also have another table which assigns accounts to groups:
account | group
--------+-------
101 | 1
102 | 1
103 | 2
I named the range of this table "groups".
Now I want to sum all payments of accounts of group 1 using only one formula, without using any additional columns. I'm trying to do that using such array formula:
sum(if(vlookup(accounts,groups,2,false)=1,amounts,0))
I also tried such array formula:
sumif(if(vlookup(accounts,groups,2,false)=1,amounts,0))
These formulas do not work. I think I know why - it seems that vlookup
function does not return array when used in one cell array formula.
So, how can I calculate it using one formula?