Sum values based on corresponding value in second column

LaurenG

Hello,

I am working on a project aiming to calculate the remaining product cost for a list of contracts.

I have two columns with data which both have multiple values separated by delimiters and need to add the values in column 1 (quantity shipped) if they match with a specific value from column 2 (dosage).

Example Data:

Product Quantity This Shipment	(Product/Material) Dosage
800, 521, 698, 685, 1,648, 413, 806, 201	100mg, 500mg, 100mg, 500mg, 100mg, 500mg, 100mg, 500mg

Anywhere 100mg is present in column 2, I need to collect and sum the corresponding value from column 1 and then I will need to perform the same action to obtain the quantity of 500mg dosage shipped. Ideally, the output would look like this:

Product Quantity This Shipment	(Product/Material) Dosage	100mg	500mg
800, 521, 698, 685, 1,648, 413, 806, 201	100mg, 500mg, 100mg, 500mg, 100mg, 500mg, 100mg, 500mg	3952	1820

TIA!!

LaurenG

https://community.smartsheet.com/discussion/comment/481059#Comment_481059

Thank you, Paul, brilliant as always! This helps me to sum the values in my shipment column, but I can't figure out how to only sum if it matches with a specific value in the dosage column.

Adding an IF statement only seems to work for one instance since I have to specify the location:
=IF(MID([Normalize Dosage]@row, FIND("~", SUBSTITUTE([Normalize Dosage]@row, "|", "~", 2)) + 1, FIND("~", SUBSTITUTE([Normalize Dosage]@row, "|", "~", 3)) - FIND("~", SUBSTITUTE([Normalize Dosage]@row, "|", "~", 2)) - 1) = "500mg", SUM(VALUE(IFERROR(MID([Normalize Qty]@row, FIND("~", SUBSTITUTE([Normalize Qty]@row, "|", "~", 2)) + 1, FIND("~", SUBSTITUTE([Normalize Qty]@row, "|", "~", 3)) - FIND("~", SUBSTITUTE([Normalize Qty]@row, "|", "~", 2)) - 1), 0))))

Any thoughts are welcome.

jmyzk_cloudsmart_jp

Hi @LaurenG

Smartsheet doesn’t currently have a SPLIT() function like Excel or Google Sheets, so when working with multiple values in a single cell (such as quantities and dosages separated by commas), we need to use helper columns and creative formulas to process and sum the data.

https://app.smartsheet.com/b/publish?EQBCT=06cfcaf186a64673839069490bb38f0c

Step 1: Pre-process the quantity values – If your numbers include commas (e.g. 1,648), first clean them using a helper column like [Prep: example - 1,648 → 1648], or preprocess them before importing.

Step 2: Normalize values – Use pipe (|) delimiters to clearly separate the values:
[Normalize Quantity] = "|" + SUBSTITUTE([Prep]@row, " ", "|") + "|"
[Normalize Dosage] = "|" + SUBSTITUTE(Dosage@row, ", ", "|") + "|"

Step 3: Extract individual items – Use MID(), FIND(), and SUBSTITUTE() to pull out each value into [Q1] to [Q8] and [D1] to [D8]. Example for Q1:
=MID([Normalize Quantity]@row, FIND("~", SUBSTITUTE([Normalize Quantity]@row, "|", "~", 1)) + 1, FIND("~", SUBSTITUTE([Normalize Quantity]@row, "|", "~", 2)) - FIND("~", SUBSTITUTE([Normalize Quantity]@row, "|", "~", 1)) - 1)

Step 4: Sum based on dosage – Now sum the values using IF() and VALUE():
[100mg] = IF([D1]@row = "100mg", VALUE([Q1]@row), 0) + IF([D2]@row = "100mg", VALUE([Q2]@row), 0) + ... + IF([D8]@row = "100mg", VALUE([Q8]@row), 0)
[500mg] = IF([D1]@row = "500mg", VALUE([Q1]@row), 0) + IF([D2]@row = "500mg", VALUE([Q2]@row), 0) + ... + IF([D8]@row = "500mg", VALUE([Q8]@row), 0)

Summary: Since Smartsheet doesn’t support text splitting natively, this formula-based method lets you simulate a split and align dosages with quantities reliably. It works well when you know the max number of entries (e.g. 8). Hopefully, Smartsheet will support a SPLIT() function in the future. Let me know if you'd like a demo sheet!

Special thanks to @Paul Newcome , who I believe invented this SUBSTITUTE method.

P/S

I used some special tools to help generate these formulas and helper columns, as doing it manually can be quite daunting. If you'd like a copy of the working demo sheet, feel free to reach out to me via the email address on my profile page. I’ll be happy to share it with you and help you get started!

LaurenG

Thank you @jmyzk_cloudsmart_jp - this is helpful although I don't know if I can operationalize it with my dataset as there is no limit to the number of times a contract can request product. For instance, I have one contract that currently has 386 separate orders and still has remaining product. Is there any way we can accomplish this without having to parse out the values?

LaurenG

https://community.smartsheet.com/discussion/comment/481001#Comment_481001

Thank you @jmyzk_cloudsmart_jp - this is helpful although I don't know if I can operationalize it with my dataset as there is no limit to the number of times a contract can request product. For instance, I have one contract that currently has 386 separate orders and still has remaining product. Is there any way we can accomplish this without having to parse out the values?

Paul Newcome

TECHNICALLY you can using a SUM and VALUE functions, but it will still require quite a few helper columns as you would have to first wrap each parsing formula in a VALUE function then nest a number of these inside of a SUM function. This provides for a very long and hard to manage formula that will end up hitting limitations very quickly (which is where the helper columns column in). You will also need to wrap each parsing formula in an IFERROR. It would end up looking something like this…

=SUM(VALUE(IFERROR(MID(1st_parsing_solution), 0)), VALUE(IFERROR(MID(2nd_parsing_solution), 0)), VALUE(IFERROR(MID(3rd_parsing_solution), 0)))

As you can see, putting in the actual "guts" for each of the parsing formulas will extend this quite rapidly, and formulas cannot contain more than 4,000 characters including spaces. This means you would need to break this down across multiple helper columns to add up (for example) parts 1 - 15, 16 - 30, 31 - 45, etc..

Then once you do all of those formulas, you would add together all of your helper columns.

@jmyzk_cloudsmart_jp I wish I could take credit for the SUBSTITUTE thing. I can't remember who I originally got this from some years ago, but I'm not the original creator for that method.

jmyzk_cloudsmart_jp

Hi @LaurenG

The demo sheet below illustrates the same principle in a vertical format.

This is a semi-automatic process, as you need to enter the order and dosage cell values into the corresponding Sheet Summary fields.

https://app.smartsheet.com/b/publish?EQBCT=9bb708191fc74368a168d7291daf4506

Column Formulas

[FIndO] =FIND("~", SUBSTITUTE(NormOrder#, "|", "~", [Row ID]@row))

[Order] =IFERROR(VALUE(MID(NormOrder#, FIndO@row + 1, INDEX(FIndO:FIndO, [Row ID]@row + 1) - FIndO@row - 1)), "")

[FindD] =FIND("~", SUBSTITUTE(NormDosage#, "|", "~", [Row ID]@row))

[Dosage] =IFERROR(MID(NormDosage#, FindD@row + 1, INDEX(FindD:FindD, [Row ID]@row + 1) - FindD@row - 1), "")

Summary Fields

100mg =SUM(COLLECT(Order:Order, Dosage:Dosage, "100mg"))
500mg =SUM(COLLECT(Order:Order, Dosage:Dosage, "500mg"))
Order (or Quantity) Enter your data
Dosage Enter your data
NormOrder ="|" + SUBSTITUTE(SUBSTITUTE(Order#, ",", ""), " ", "|") + "|"
NormDosage ="|" + SUBSTITUTE(Dosage#, ", ", "|") + "|"

Below is the image of a solution using Google Sheets, where we can use the SPLIT function. As we use the identical 386 quantity/dosage pairs, we get the same result.

For simplicity, I used seven rows per order. However, as Google Sheets' column number limit is 18,278, or up to the ZZZ column, we can use 500 columns per row, allowing us to handle orders in a row with 3500 or more, plus several additional columns.

https://docs.google.com/spreadsheets/d/19ABLr7CdDf3LkztXAFSzEa_IOazNwcbqENuKdY8_-IU/edit?gid=0#gid=0

LaurenG

https://community.smartsheet.com/discussion/comment/481059#Comment_481059

Thank you, Paul, brilliant as always! This helps me to sum the values in my shipment column, but I can't figure out how to only sum if it matches with a specific value in the dosage column.

Adding an IF statement only seems to work for one instance since I have to specify the location:
=IF(MID([Normalize Dosage]@row, FIND("~", SUBSTITUTE([Normalize Dosage]@row, "|", "~", 2)) + 1, FIND("~", SUBSTITUTE([Normalize Dosage]@row, "|", "~", 3)) - FIND("~", SUBSTITUTE([Normalize Dosage]@row, "|", "~", 2)) - 1) = "500mg", SUM(VALUE(IFERROR(MID([Normalize Qty]@row, FIND("~", SUBSTITUTE([Normalize Qty]@row, "|", "~", 2)) + 1, FIND("~", SUBSTITUTE([Normalize Qty]@row, "|", "~", 3)) - FIND("~", SUBSTITUTE([Normalize Qty]@row, "|", "~", 2)) - 1), 0))))

Any thoughts are welcome.

LaurenG

@Paul Newcome and @jmyzk_cloudsmart_jp- Thank you both for your help! I was able to find a solution by combining your two suggestions and adding in an appropriately placed IF statement.

=SUM(IF(MID([Normalize Dosage]@row, FIND("~", SUBSTITUTE([Normalize Dosage]@row, "|", "~", 1)) + 1, FIND("~", SUBSTITUTE([Normalize Dosage]@row, "|", "~", 2)) - FIND("~", SUBSTITUTE([Normalize Dosage]@row, "|", "~", 1)) - 1) = "500mg", VALUE(IFERROR(MID([Normalize Qty]@row, FIND("~", SUBSTITUTE([Normalize Qty]@row, "|", "~", 1)) + 1, FIND("~", SUBSTITUTE([Normalize Qty]@row, "|", "~", 2)) - FIND("~", SUBSTITUTE([Normalize Qty]@row, "|", "~", 1)) - 1), 0))), IF(MID([Normalize Dosage]@row, FIND("~", SUBSTITUTE([Normalize Dosage]@row, "|", "~", 2)) + 1, FIND("~", SUBSTITUTE([Normalize Dosage]@row, "|", "~", 3)) - FIND("~", SUBSTITUTE([Normalize Dosage]@row, "|", "~", 2)) - 1) = "500mg", VALUE(IFERROR(MID([Normalize Qty]@row, FIND("~", SUBSTITUTE([Normalize Qty]@row, "|", "~", 2)) + 1, FIND("~", SUBSTITUTE([Normalize Qty]@row, "|", "~", 3)) - FIND("~", SUBSTITUTE([Normalize Qty]@row, "|", "~", 2)) - 1), 0))))

jmyzk_cloudsmart_jp

Hi @LauraG

Thanks again for your continued updates. I wanted to share a few observations and suggestions based on your latest formula and automation approach.

Formula Scope – Only Covers 2 Items

Your formula:

=SUM(  IF(... dosage1 = "500mg", quantity1),  IF(... dosage2 = "500mg", quantity2))

currently only processes the first two dosage/quantity pairs. If you're working with a large number of items like 386, the remaining 384 pairs are not included in the calculation unless you manually add dozens or hundreds of IF(MID(...)) blocks. Each block typically uses 280–320 characters. At that rate, even handling just 15 items could exceed Smartsheet’s 4,000-character formula limit per cell. Attempting to process all 386 pairs with this approach would easily go beyond 100,000 characters, which is not feasible in Smartsheet.

Column Limit – Horizontal Parsing Hits Hard Limits

To avoid the 4,000-character limit, one common workaround is to split each quantity and dosage using many helper columns like [Q1], [Q2], [D1], [D2], etc. Then, you use lightweight conditional formulas like =IF([D1]@row = "500mg", VALUE([Q1]@row), 0) across each pair. This works well in smaller scenarios and keeps formulas simple.

However, Smartsheet has a 400-column maximum per sheet. For 386 quantity/dosage pairs, you'd need 772 columns just for the split values — and more if you include flags, totals, or additional processing logic. That makes this approach unsustainable for larger orders. For anything over ~180 pairs, you'd exceed the column limit, making a horizontal layout unsuitable for scalable use.

Clarifying the “386 Separate Orders” Statement

Earlier you mentioned, “I have one contract that currently has 386 separate orders.” I interpreted this as meaning you have a single row that contains 386 comma-separated quantities and 386 comma-separated dosages (in Order# and Dosage# fields), rather than 386 separate rows. If that's correct, your use case involves parsing long strings in a single row, and not aggregating across rows — which further reinforces the need for a row-based solution to avoid column and formula limits.

Robust Alternative – Vertical Parsing with Sheet Summary + Automation

A more scalable approach is to use the first 620 rows (in my example, or any sufficiently high number) as a fixed "calculation zone," with each row representing one quantity/dosage position in the order. Each row is assigned a [Row ID], and formulas extract the value based on that position using:

=FIND("~", SUBSTITUTE(NormOrder#, "|", "~", [Row ID]@row))

and

=IFERROR(VALUE(MID(NormOrder#, FindO@row + 1, INDEX(FindO:FindO, [Row ID]@row + 1) - FindO@row - 1)), "")

https://app.smartsheet.com/b/publish?EQBCT=b8830d92739248beaf499b274ec830f6

~ ~ ~

The [Last Row] formula identifies the final calculation row in use with:

=MAX([Row ID]:[Row ID]) = [Row ID]@row

To support this logic, I use Sheet Summary fields like NormOrder# and NormDosage#, which normalize the original input. For example, NormOrder# is:

="|" + SUBSTITUTE(SUBSTITUTE(Order#, ",", ""), " ", "|") + "|"

This removes thousand separators and converts delimiters into pipe symbols for easy MID-based splitting. Similarly, NormDosage# applies the same pattern to dosage inputs. Once the values are split and assigned to individual rows, [100mg]# and [500mg]# summary fields use:

=SUM(COLLECT(Order:Order, Dosage:Dosage, "100mg"))

and the same for "500mg" — these summarize values only from active calculation rows.

In my previous version of this solution, users had to manually copy the quantity and dosage string into two Sheet Summary fields before the calculation could happen. In the revised approach, I’ve automated this step — the system checks for the [Last Row] using a MAX function and then pulls in the corresponding values from the correct columns using cell-linked summary formulas. This eliminates the need for users to manually input or move the data to the summary section.

To control when the totals become actionable, I mirror the summary value into a row field only if [Last Row]@row is true:

[100mg] =IF([Last Row]@row, [100mg]#)

and similarly for [500mg].

A [has Value] checkbox triggers when either total is greater than 0:

=OR([100mg]@row > 0, [500mg]@row > 0)

With this check, the automation described later will work, if all the items are 100mg only, for example.

This design supports full automation: when [100mg] or [500mg] changes to any value, a workflow automation copies the row to a results sheet, serving as your order record log. Since all parsing and totals are controlled through formulas, there's no need to manually edit sheet summary fields or trigger calculations by hand.

https://app.smartsheet.com/b/publish?EQBCT=675d1222336a4f02a9bc4108897e87d4 (Copy rows destination sheet)

Let me know if you’d like access to a working demo of this approach. It fully supports orders with hundreds of items and avoids both column count and formula character limits — and it’s designed to work cleanly with Smartsheet automation.

LaurenG

https://community.smartsheet.com/discussion/comment/481160#Comment_481160

If you can please share the demo space, that would be helpful as I have 75 rows each with multiple orders. I checked again yesterday and one row has more than 900 orders, so I will need a solution that won't exceed Smartsheet's limits. Thank you.

jmyzk_cloudsmart_jp

HI @LauraG

I am happy to share the demo workspace. Please get in touch with me by email, as I need your email address to share the space with you. (My email address is in my profile page.)