google-sheetsgoogle-sheets-formula

Using a multi-select cell to create a total Sum in Google Sheets formula

I've got a google sheet seen here

| Column A | Column B | Column C |
| -------- | -------- | -------- |
| Product  | $100     ||
| Add-on 1 | $20      ||
| Add-on 2 | $10      ||
| Add-on 3 | $5       ||
||||
||||
|Customer. | Add-ons  | Total Due |
| John Doe | Multi-select | Formula Needed here |

Google sheet:

Google Sheet Image

I'm trying to write a formula in cell C8 that will allow me to multi-select in B8 and get a total in C8.

Here's what I have tried

=B1 + SUMPRODUCT(--(A2:A4=TRANSPOSE(B8)),B2:B4)

If I select one item in B8, it creates a sum as expected. But when I select 2 items, the value returns to just the value of B1. I suppose that is because once I select two values in B8 it becomes a list instead and this formula doesn't know how to deal with that.

Is there a way to solve this inside the cell formula? Or will I need to do this in apps script?

Solution

  • Try these Google sheets formula instead:

    =B1 + SUM(BYROW(TOCOL(SPLIT(B8,",")), LAMBDA(r, FILTER(B2:B4,A2:A4 = trim(r)))))

    or if you want to multiply each value, use this:

    =B1 + PRODUCT(BYROW(TOCOL(SPLIT(B8,",")), LAMBDA(r, FILTER(B2:B4,A2:A4 = trim(r)))))

    References: