google-sheets
Calculating the overall value of the cell based on the dictionary in google sheets
I have a value in one cell looking something like this: (The product name and the amount required)
[Product1] 1, [Product2] 2, [Product3] 3
As well i have a dictionary looking something like: (Each product value)
| A | B |
|---|---|
| Product1 | 100 |
| Product2 | 125 |
| Product3 | 225 |
Im curious to know if its possible to get the overall value of the cell's products? So in that case i need to get a value of 1025
Honestly didnt tried much, becuase im not that familiar with google sheets. Don't even know how to approach the problem.
Solution
You may try:
=let(Σ,index(substitute(split(tocol(split(A2,", ",)),"] ",),"[",)),
sumproduct(ifna(vlookup(index(Σ,,1),E:F,2,)),index(Σ,,2)))
- Multiple submission at exact same time in Google App Script
- Hello world menu and sidebar for Google Workspace Add-On for Google Sheets
- Did Google Sheets stop allowing json access?
- How to Run Script on Multiple Spreadsheet Tabs
- Filtering data from other google Spreadsheet sheet based on a combo value from other sheet in the same spreadsheet
- Can I count occurrences of each unique item and then sort by that count
- Check if Cell Contains Month and Compare to Date
- Arrayformula with Query in Google Sheets
- Apps Script: why does name of properties cause "Maximum call stack size exceeded"?
- Conditional borders troubleshooting
- Troubleshooting Spreadsheet Form Submit Events Google
- Macro for replacing data and then collating it?
- QUERY Formula referring to the value of a cell rather than a constant piece of text
- Index Function - Use One Formula to Generate the Result List with Two Variables
- google sheets IMPORTXML for currency prices
- How to add plus 5 hour to the copy Cell and past in other with plus time
- How to connect Google Sheets to Database
- Merging Google Sheets formulas into1 to get the same Result
- TypeError: UrlFetchApp.fetch(...).then is not a function
- Using Column Headers with COUNTIF To Add those that Fall Between Dates in Cells
- Using SUMIFS with MAP/LAMBDA
- Deal with line break while using IMPORTXML in Google Sheet
- What is an equivalent for INDEX in ARRAYFORMULA?
- Google Sheet Script - Removing a gap of blank cells within a range to keep a list compact and tidy?
- For loop running only for first element
- How can I use a Custom Function in Formulas across Multiple Spreadsheets?
- How to use a filter inside SortN
- Problems with ShinyApp and Google authentification, when making Public
- Newbie Help: Syntax error: SyntaxError: Unexpected identifier (JavaScript)
- Error in Google App Script when submitting form data to Google Sheets