How to sum up multiple VLOOKUP results?
I've got two lists: one with items and one with orders
| Item | Price |
|---|---|
| 1 | 10.10 |
| 2 | 2.02 |
| Order | Item 1 | Item 2 | ... | Item 15 | Sum |
|---|---|---|---|---|---|
| A | 1 | 10.10 | |||
| B | 1 | 2 | 12.12 |
For each order I want to sum up the prices of the items listed in that row (see last column).
I've tried searching for a formula but everything I've found refers to a different problem. Either they want to sum up multiple columns of a single result or they want to look up multiple rows based on a single value. And I've not been able to adapt those formulas or ideas to my problem.
Solution
Use MAP() then SUM(). Try-
=SUM(MAP(E2:H3,LAMBDA(x,XLOOKUP(x,A2:A,B2:B,0))))
Edit: After comment by OP.
Try-
=BYROW(E2:H3,LAMBDA(r,SUM(MAP(r,LAMBDA(x,XLOOKUP(x,A2:A,B2:B,0))))))
And full dynamic formula to refer all rows.
=BYROW(E2:H,LAMBDA(r,IF(COUNTIFS(r,">0")=0,,SUM(MAP(r,LAMBDA(x,XLOOKUP(x,A2:A,B2:B,0)))))))
- How to create a formula that will repeat a range by splitting one column and joining with the other column?
- Google Sheets Splitting Text to Rows and Duplicate Surrounding Rows Entries
- How to transpose & split multiple columns and repeat specific cells in a column
- How do you deal with multiple values in a single cell separated by comma?
- Copy specific fields to another sheet
- Find Text and copy another cells
- Generating date range based on allowed weekdays, repeat times and times of day in Google Sheets
- How to use a query within a query?
- Find first empty row and scroll to it, Lastrow not working due to formulas
- Map a table, replacing values based on other cell
- Sum value if matching value is not blank in range leaving it blank if empty
- A Script to Simplify Creating a SO Table
- Google Sheets API v4 and valueInputOption
- Get Google Sheet by ID?
- ReferenceError: C is not defined
- How to sum up multiple VLOOKUP results?
- Conditional Formatting in Google Sheets with another table INDIRECT
- How to reference a cell to the left of active cell in Google script
- How to show instruction with image after install google workspace extension?
- How to use SpreadsheetApp.Spreadsheet.getChanges method?
- Get the user that changed specific cell
- Daytime and Nightime Occurrence Duration of an Event
- Recombining 4 pairs of numbers
- GmailApp.sendEmail: is it possible to schedule send an email?
- Why do I get the error SPLIT parameter 1 empty error when it isn't?
- Is there a faster way to sort sheets with AppScripts?
- ISO-8601 String to Date in Google Sheets cell
- How can we sort multiple values ​horizontally using VLOOKUP?
- COUNTIFS WITHIN DATE RANGE GOOGLE SHEET
- conditional formatting on a single cell where criteria is another column [solved]