# Excel SUMIF account currency matches the one being counted

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:

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?

Use SUMPRODUCT:

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

• 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

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.