Help With Summing some columns and helper columns
Hello! I have a rather odd situation I am trying to find a solution for but having some trouble.
Columns:
- Column A
- This is a text field that I am pulling out text from with a MID function to get a count
- I cannot edit this data as it is being imported from an outside source
- Column B
- This is a Formula column I am pulling out the quantity from with the following formula
- =IFERROR(VALUE(MID(Column A]@row, (10 + FIND("Quantity: ", [Column A]@row)), 1)), 0)
Issues:
- I had to add the value into the formula so that SUM functions would work properly
- If the quantity is greater than 10 the formula above breaks because it no longer contains a value
- If I add "Column C" I can make it so that one has the count for more than 2 digits, but I am having trouble coming up with a formula to add the columns together properly
Any help would be much appreciated. 😁
Best Answer
-
It did end up being a single misplaced parenthesis. Sorry abouut that.
=IFERROR(VALUE(MID([Column A]@row, 10 + FIND("Quantity: ", [Column A]@row), FIND(CHAR(10), [Column A]@row, FIND("Quantity: ", [Column A]@row)) - (10 + FIND("Quantity: ", [Column A]@row)))), 0)
Answers
-
Are you able to provide some screenshots for context?
-
In this example the QTY should be 50 but because of the character issues its coming up 55
-
Where is "Column A"? I'm not sure I follow exactly what you are trying to do?
-
My Apologies, I cannot show everything as there is some personal information in the feilds,
Column A is formatted like this
In column B (QTY1) I was trying to pull out the number after "Quantity: " which I am able to accomplish with this formula
- =IFERROR(VALUE(MID(Column A]@row, (10 + FIND("Quantity: ", [Column A]@row)), 1)), 0)
This formula fails if the quantity is greater than 10 due to having 2 digits. like the example below
So I tried to correct this by using Column C (Qty1-1) by using the following formula
- =IFERROR(VALUE(MID(Column A]@row, (10 + FIND("Quantity: ", [Column A]@row)), 2)), 0)
But this fails on every device with a quantity less than 10 due to it looking for the second digit
So I was trying to figure out a smart way to do this so I can get the number correctly but just can't wrap my head around it.
I know its a lot thanks for your help
-
Ah. Understood. Seeing the data in Column A helps.
First we find the starting point (which you have). Then we find the line break after (which is CHAR(10) in formulas) and subtract from it the starting point.
Starting Point:
(10 + FIND("Quantity: ", [Column A]@row))
Line break after starting point:
FIND(CHAR(10), [Column A]@row, FIND("Quantity: ", [Column A]@row)
Subtract start from end:
FIND(CHAR(10), [Column A]@row, FIND("Quantity: ", [Column A]@row) - (10 + FIND("Quantity: ", [Column A]@row))
Use that as the number of characters in the MID function to make it more dynamic:
=IFERROR(VALUE(MID(Column A]@row, (10 + FIND("Quantity: ", [Column A]@row)), FIND(CHAR(10), [Column A]@row, FIND("Quantity: ", [Column A]@row) - (10 + FIND("Quantity: ", [Column A]@row)))), 0)
-
Thanks Paul!
I was having some trouble putting that in, it tells me the Syntax is not quite right, is there some parenthesis I may need to move?
-
What do you get if you plug it in as a cell formula instead of a column formula?
-
#Incorrect Argument Set
Here is a test sheet with examples in it
https://app.smartsheet.com/b/publish?EQBCT=95c7ad94e69149d18f3e694c45bacaab
-
It did end up being a single misplaced parenthesis. Sorry abouut that.
=IFERROR(VALUE(MID([Column A]@row, 10 + FIND("Quantity: ", [Column A]@row), FIND(CHAR(10), [Column A]@row, FIND("Quantity: ", [Column A]@row)) - (10 + FIND("Quantity: ", [Column A]@row)))), 0)
-
Thank you so much, you sir are a LEGEND!
😁
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.7K Get Help
- 406 Global Discussions
- 218 Industry Talk
- 456 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!