Google Sheets: How do I reference cell values with text from another cell?-CodePudding

I want to make a list of, say, grocery items. In one column I have the items, in another column I have the costs. I want to create a list with a subset of the grocery items such that the total price of those items is returned.

I've attached a screenshot with an example to demonstrate. The items are in column A; the costs are in column B. The subset of items I want to purchase is in column D; and I want the total cost returned in column E. I want to be able to add or subtract items from Column D and have the total cost update in column E.

Is there a way to do this with functions or minimal scripting? I've looked at documentation for INDIRECT and HLOOKUP, but I don't know these are the functions that I need, and if I do, I don't know how I need to use them.

CodePudding user response：

Try this

=INDEX(SUM(SUMIF(O2:P8,"="&R2:R5,P2:P8)))

CodePudding user response：

The solution that worked for me was to use VLOOKUP with ARRAYFORMULA:

 =SUM(ARRAYFORMULA(IF(ISBLANK(D2:D),,VLOOKUP(D2:D,A:B, 2,))))

CodePudding user response：

try:

=INDEX(SUM(1*IFERROR(VLOOKUP(D2:D; A:B; 2; ))))