Google-sheets – If cell contains a text string assign a value to another cell

formulasgoogle sheetsgoogle-sheets-arrayformulavlookup

I have a number of "shopping" cells formatted to have a dropdown list of products/words using the (right-click) "data validation" function.

I have another cell I want to come up with a total price of all the products chosen from the various "shopping" cells, given each product/word has a defined number value/price.

e.g.

cell 1 chooses "widget1"
cell 2 chooses "widget4"
cell 3 chooses "widget7"

widget1 costs 10
widget4 costs 13
widget7 costs 25

Finally, I want a total price cell to show the total price of all the products chosen.

Best Answer

={"Price";
 ARRAYFORMULA(IF(LEN(A8:A),
 IFERROR(VLOOKUP(A8:A, E2:F4, 2, 0), 0)+
 IFERROR(VLOOKUP(B8:B, G2:H4, 2, 0), 0)+
 IFERROR(VLOOKUP(C8:C, I2:J4, 2, 0), 0), ))}

Related Solutions

Google-sheets – See if a cell contains a string not from a list of strings in another column

You can do this by applying to range A1:A the conditional formatting with custom formula

=counta(unique(transpose(split(A1&", "&JOIN(", ",B$1:B),", "))))>counta(unique(B$1:B))

Explanation.

To the right of the inequality sign, counta(unique(B$1:B)) counts the number of distinct words in column B.
On the left, A1&", "&JOIN(", ",B$1:B) concatenates A1 with the content of column B, comma-and-space separated.
Then split(...,", ") separates all of this into a row of words
Then counta(unique(transpose(...))) counts the number of distinct words among these.
Formatting is applied if the second count is greater than the first.

Google-sheets – If cell values match a string, add some values

You can use arrayformula with SUM to do look up only the needed values like this:

=SUM(ARRAYFORMULA(if(B2:D2="☑", vlookup($B$1:$D$1,G:H,2,false),)))

Best Answer

Related Solutions

Google-sheets – See if a cell contains a string not from a list of strings in another column

Google-sheets – If cell values match a string, add some values

Related Topic