0

I have data as follows:

enter image description here

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?

2 Answers 2

2

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.

enter image description here

3
  • 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
1

Try this formula:

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

enter image description here

You must log in to answer this question.

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