Stumped on formula to calculate sum of numbers found within a number series

sawuzie

Hello, I am having trouble with this formula, which came from a previous thread that I received help with:

https://community.smartsheet.com/discussion/107702/find-the-sum-of-a-number-string-contained-in-a-series-of-numbers

The formula worked when Study Impacted Med ID's referenced the entire column ( [Study Impacted Med ID’s]:[Study Impacted Med ID’s] ), but now that I would like it be [Study Impacted Med ID’s]@row, it's having issues. At first it was #incorrect argument, but after changing my references it is #unparseable, which means my syntax is off somewhere.

=SUM(COLLECT({Excursion Time}, [Study Impacted Med ID’s]@row, CONTAINS([Med ID]@row + " ", LEFT(@cell, LEN([Med ID]@row) + 1)))) + SUM(COLLECT({Excursion Time}, [Study Impacted Med ID’s]@row, CONTAINS(" " + [Med ID]@row + " ", @cell))) + SUM(COLLECT({Excursion Time}, [Study Impacted Med ID’s]@row, CONTAINS(" " + [Med ID]@row, RIGHT(@cell, LEN([Med ID]@row) + 1)))) + SUM(COLLECT({Excursion Time}, [Study Impacted Med ID’s]@row, HAS([Med ID]@row, @cell)))

I have been struggling to find the issue for a while, so any help with this would be highly appreciated! Please let me know anything I should clarify as well

Debbie Sawyer

At the start of your formula where it says:

=SUM(COLLECT({Excursion Time},

Are you summing a cross sheet reference called {Excursion Time} or are you referring to the column in the screenshot called {Excursion Times}?

If you are referring to the column in the same sheet, then you might need to change

=SUM(COLLECT({Excursion Time}, [Study Impacted Med ID’s]@row, CONTAINS([Med ID]@row + " ", LEFT(@cell, LEN([Med ID]@row) + 1)))) + SUM(COLLECT({Excursion Time}, etc...

to

=SUM(COLLECT([{Excursion Times}]:[{Excursion Times}], [Study Impacted Med ID’s]@row, CONTAINS([Med ID]@row + " ", LEFT(@cell, LEN([Med ID]@row) + 1)))) + SUM(COLLECT([{Excursion Times}]:[{Excursion Times}], etc..

Very confusing to use {} as part of a column name!!

Does this make any sense?

Kind regards

Debbie

sawuzie

Hello @Debbie Sawyer! Yes the {Excursion time} is a cross sheet reference, I just included it in the screenshot rather than taking a screenshot of two sheets.

I’ve since realized the issue is actually that the criterion range of my collect function cannot be an @row argument, because a range must include more than one cell.

I’m really hoping there is a work around for the criterion range to only consider the [Study Impacted Med ID’s] at row, rather than the whole column.

Sawuzie

Paul Newcome

You can use @row in the criteria. And you can use it as a range so long as all ranges within the same function are the same shape and size.

Based on the fact that no cell references are highlighted, my first guess would be a column name doesn't match, and my second guess would be a misplaced or missing quote, and my third guess would be a misplaced or extra parenthesis.

Additional Note On Column Names:

Smartsheet "hides" extra spaces, but stores them on the back end. So if you have two spaced between two words, it will only appear as one. But since it is stored on the back end as two, unless you have those two spaces, it won't be considered a match on the column name. When typing out your formula, click on the cell in question instead of typing out the column name yourself.

Once you get the unparseable error sorted though, there is still the incorrect argument error to tackle. All of your ranges should be cross sheet references.

[Study Impacted Med ID’s]@row

Should be a cross sheet reference to the column containing that data in the source sheet.

sawuzie

Must have missed these tips on Smartsheet University, thank you so much for these helpful facts!!