0

This is related to a previous question I asked. There, I made the mistake of oversimplifying in the example I used.

In contrast to that example, where each account balance appears once and in order, in reality there are many un-ordered deposits to deal with:

enter image description here

As mentioned before, I have already made several attempts, however all of them result in an error.

SUMIF(DepositAccounts, VLOOKUP(F2, AccountsWithCurrencies, 2, FALSE)="GBP", DepositAmounts)

Where DepositAccounts refers to E3:E7, AccountsWithCurrencies to B3:C5 and DepositAmounts to F3:F7.

What can I do?

2 Answers 2

2

Use SUMPRODUCT:

=SUMPRODUCT($F$3:$F$7*(VLOOKUP($E$3:$E$7,B:C,2,FALSE)=H3))

enter image description here

2
  • This works great! I would like to apply it to the entire columns E and F, to allow for future entry of deposits. How do I prevent the #N/A error? Aug 31, 2020 at 18:42
  • That is a different question than the one above. Aug 31, 2020 at 18:44
0

I'm sure there are other ways, but you can use this:

=SUM(SUMIF(deposits[Account],FILTER(accounts[Account],accounts[Currency]=$H3),deposits[Deposit]))

The important thing here is that if you want to add more accounts or more deposits, format those lists as Tables. Put your cursor in the list and press Ctrl+T, then use the Table Design tab to name the table. I've named them accounts (B2:C5) and deposits (E2:F7). As new rows are added, the formula above will always pick them up.

enter image description here

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .