Find the sum of a number string contained in a series of numbers

sawuzie

Hello!

I am currently trying to find the amount of times the entry @row in [Med ID's with space] can be found in the entire [(Cell Linked from TE Tracker)] column. Then I would like the sum of the [Excursion Time (min)] of all the [(Cell Linked from TE Tracker)] cells that the [Med ID's with space]@row was found in.

As you can see, the current formula I am using pulls the entire sum of [Excursion Time (min)] no matter the row, because I cant figure out where to incorporate the @row. Any help with how to adjust this would be appreciated!

Thank you

Carson Penticuff

If I understand what you are attempting to do, this should work. Most of the complexity is due to "false" matches that could occur. For instance, the bottom entry on your screenshot, "2345" would match "2345", "12345", "23456", or any other group of numbers that contain those consecutive four digits. It is also not possible to only search for those digits surrounded by spaces, " 2345 ", as that would not catch cells that contained the entry as the first or last group in a cell.

=SUM(COLLECT([Excursion Time (min)]:[Excursion Time (min)], [(Cell Linked from TE Tracker)]:[(Cell Linked from TE Tracker)], CONTAINS([Med ID's with space]@row + " ", LEFT(@cell, LEN([Med ID's with space]@row) + 1)))) + SUM(COLLECT([Excursion Time (min)]:[Excursion Time (min)], [(Cell Linked from TE Tracker)]:[(Cell Linked from TE Tracker)], CONTAINS(" " + [Med ID's with space]@row + " ",@cell))) + SUM(COLLECT([Excursion Time (min)]:[Excursion Time (min)], [(Cell Linked from TE Tracker)]:[(Cell Linked from TE Tracker)], CONTAINS(" " + [Med ID's with space]@row, RIGHT(@cell, LEN([Med ID's with space]@row) + 1))))

sawuzie

@Carson Penticuff This formula worked perfectly, and I was also concerned about the false matches as well. My plan was to use the leading and trailing edge method but your explanation is very true as well. I can't thank you enough!

Carson Penticuff

I was just looking at your other post about adding spaces at the start and end of cells and had a suspicion that was the reason! I'm glad I could help!

sawuzie

Hello again @Carson Penticuff !

Is it possible to adjust this formula so that it can catch entries within [(Cell Linked from TE Tracker)] that do not have spaces on at least one side of them? I know this is different from my original question.

The cells within [(Cell Linked from TE Tracker)] containing "12345" should have a sum of 1859, but rather have 1759 because the last entry in the column is not counted. What is the best way to approach this?

Carson Penticuff

This should take cells with a single grouping into account:

=SUM(COLLECT([Excursion Time (min)]:[Excursion Time (min)], [(Cell Linked from TE Tracker)]:[(Cell Linked from TE Tracker)], CONTAINS([Med ID's with space]@row + " ", LEFT(@cell, LEN([Med ID's with space]@row) + 1)))) + SUM(COLLECT([Excursion Time (min)]:[Excursion Time (min)], [(Cell Linked from TE Tracker)]:[(Cell Linked from TE Tracker)], CONTAINS(" " + [Med ID's with space]@row + " ", @cell))) + SUM(COLLECT([Excursion Time (min)]:[Excursion Time (min)], [(Cell Linked from TE Tracker)]:[(Cell Linked from TE Tracker)], CONTAINS(" " + [Med ID's with space]@row, RIGHT(@cell, LEN([Med ID's with space]@row) + 1)))) + SUM(COLLECT([Excursion Time (min)]:[Excursion Time (min)], [(Cell Linked from TE Tracker)]:[(Cell Linked from TE Tracker)], HAS([Med ID's with space]@row, @cell)))

sawuzie

Thank you, this even helped me with a similar cumulative measurement column I had!! But I do have one more request! Rather than the entire [(Cell Linked from TE Tracker)] column being the criterion range of the collect function, is it possible for it to be the [(Cell Linked from TE Tracker)]@row instead?

I tried replacing [(Cell Linked from TE Tracker)]:[(Cell Linked from TE Tracker)] with [(Cell Linked from TE Tracker)]@row and it gave me incorrect argument.

=SUM(COLLECT([Excursion Time (min)]:[Excursion Time (min)], [(Cell Linked from TE Tracker)]@row, CONTAINS([Med ID's with space]@row + " ", LEFT(@cell, LEN([Med ID's with space]@row) + 1)))) + SUM(COLLECT([Excursion Time (min)]:[Excursion Time (min)], [(Cell Linked from TE Tracker)]@row, CONTAINS(" " + [Med ID's with space]@row + " ", @cell))) + SUM(COLLECT([Excursion Time (min)]:[Excursion Time (min)], [(Cell Linked from TE Tracker)]@row, CONTAINS(" " + [Med ID's with space]@row, RIGHT(@cell, LEN([Med ID's with space]@row) + 1)))) + SUM(COLLECT([Excursion Time (min)]:[Excursion Time (min)], [(Cell Linked from TE Tracker)]@row, HAS([Med ID's with space]@row, @cell)))