CODEWITHSUNDEEP
google-sheets-formula
arlovande
arlovande

Reputation: 135

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?

Upvotes: 0

Views: 62

Answers (3)

FikturFox
FikturFox

Reputation: 1

You may try this too:

=B1 + Sum(Index(if((Trim(Split(B8,",")))=A2:A4,B2:B4,0)))

Upvotes: 0

rockinfreakshow
rockinfreakshow

Reputation: 30215

Try this modification:

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

Upvotes: 0

EL SRY
EL SRY

Reputation: 1031

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:

Upvotes: 1

Related Questions