How to write a formula in Google Spreadsheet that will count sum of multiplied values using VLOOKUP function

user_19511816 0
Viewed 64

I have created a spreadsheet to track my crypto portfolio.

Here is the simple view of the task, that I'm trying to solve:

spreadsheet sample with the description

I want to:

  1. Take the number (amount) from each cell (A3:C3)
  2. Take the Currency name (A2:C2) that is related to the number in A3:C3
  3. VLOOKUP the data for EACH currency (F2:F4) and "*" amount (A3:C3) and the current price (G2:G4) accordingly
  4. The final step is to Sum all multiplied values and return the TOTAL SUM

So, basically, it works for me if I use the formula like that in my original sheet:

=(O8*(VLOOKUP(O$1,AD$2:AE,2,FALSE)))+(P8*(VLOOKUP(P$1,AD$2:AE,2,FALSE)))+(Q8*(VLOOKUP(Q$1,AD$2:AE,2,FALSE)))+(R8*(VLOOKUP(R$1,AD$2:AE,2,FALSE)))+(S8*(VLOOKUP(S$1,AD$2:AE,2,FALSE)))+(T8*(VLOOKUP(T$1,AD$2:AE,2,FALSE)))+(U8/(VLOOKUP(U$1,AF$2:AG,2,FALSE)))+(W8*(VLOOKUP(W$1,AD$2:AE,2,FALSE)))+(X8*(VLOOKUP(X$1,AD$2:AE,2,FALSE)))+(Y8*(VLOOKUP(Y$1,AD$2:AE,2,FALSE)))

However, this is too long and I'd like to find a better way to do this function

The problem with the current formula is that if I add a new wallet/currency between A:C (according to the sample screenshot), the whole formula will break because it won't include a new value. That's why I'm looking for a better formula to be able to insert the new column between A:C (so it becomes A:D) and the total SUM works in a way =sum(A:D)

No matter, how many cells I insert between A:D, the final function will work

If I use the long function like above, I have to rewrite many other cells to include a new column function once I add a new column.

Please let me know if you have any questions.

vlookup google-sheets-formula flatten transpose google-sheets
1 Answers

try:

=SUMPRODUCT(QUERY({TRANSPOSE(A3:C4), 
 IFNA(VLOOKUP(FLATTEN(A3:C3), F:G, 2, ))}, 
 "select Col2*Col3"))

enter image description here

=ARRAYFORMULA(MMULT(IF(B6:G10="", 0, B6:G10*HLOOKUP(B4:G4, 
 TRANSPOSE(J2:K10), 2, 0)), SEQUENCE(COLUMNS(B6:G10), 1, 1, 0)))

enter image description here

user_56326290
Related