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:

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?

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:

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?

• google-sheets-formula

2 Answers 2

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:

• BYROW()
• FILTER()

Try this modification:

=B1+sumproduct(ifna(xmatch(A2:A4,split(B8,", ",))^0),B2:B4)