# Excel SUMIF VLOOKUP matches value

I have data as follows:

What I want is to fill out the Total cells for each currency by adding up each Balance cell for which the currency of the account (above) matches that currency. So in this example, cell `F6` (the total for GBP) would end up having value 12 (the sum of the HSBC balance and the Barclays balance).

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

``````SUMIF(AccountNames, VLOOKUP(F2, AccountsWithCurrencies, 2, FALSE)="GBP", AccountBalances)
``````

Where `AccountNames` refers to the range `F2:H2`, `AccountBalances` is `F3:H3`, and AccountsWithCurrencies is `B3:C5`.

I have seen various suggestions use `SUMPRODUCTS` but I cannot understand them. What can I do?

You can use this formula:

``````=SUM(\$F\$3:\$H\$3*TRANSPOSE(--(\$C\$3:\$C\$5=F\$5)))
``````

To break it down, `\$C\$3:\$C\$5=F\$5` will create a 3-item array of TRUE or FALSE, depending on whether the value in column C matches the value in F5 or not. Doing this `--(\$C\$3:\$C\$5=F\$5)` changes the TRUE to 1 and the FALSE to 0, so in the case of GBP, you have a 3-item array of {1;1;0}. Note that the semi-colon indicates that the array is vertical. We use `TRANSPOSE` to convert this to {1,1,0} (i.e. a horizontal array). We convert it to a horizontal array so we can multiply it by the horizontal range F3:H3.

Since the values in F3:H3 are {10,2,6}, we are then essentially doing this:

``````{10,2,6} * {1,1,0}
``````

Which of course evaluates to {10,2,0}. Wrapping that with the SUM function gives you the correct result for GBP.

• This must be an Array formula, otherwise return #VALUE error ! Aug 30, 2020 at 7:23
• Not necessarily Aug 30, 2020 at 10:40
• Thanks for the help, your answer perfectly solved the given problem, however I made the mistake of oversimplifying. Please check out the new thread! Aug 31, 2020 at 18:31

Try this formula:

``````=SUMPRODUCT((\$C\$3:\$C\$5=F5)*(\$B\$3:\$B\$5=\$F\$2:\$H\$2)*\$F\$3:\$H\$3)
``````