Formula Help!
Hi,
I am trying to edit an existing formula in SmartSheet to add another criteria and it won't work! In the same row, I want to add (LAL@row * 131.25) in addition to the formula below. For some reason it breaks all of the formula. Please help!
Best Answer
-
Ah. There's the problem. You need to be referencing cells. What you really need is a series of COUNTIFS added together.
=(COUNTIFS([1]@row:[31]@row, "SUP") * 250) + (COUNTIFS([1]@row:[31]@row, "LH") * 175)
Answers
-
Hi @Clare C
I hope you're well and safe!
Can you share some screenshots which include the columns used in the formula? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help.
I hope that helps!
Be safe, and have a fantastic week!
Best,
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Lets start by getting rid of the parenthesis that aren't needed and adjusting the syntax a little bit...
=SUM(SUP@row * 250, LH@row * 175, FC@row * 150, ...................)
Once you get rid of all of those parenthesis and replace the plusses with commas, try adding in the new set following the same syntax.
-
Thanks Paul, gave that a go and it didn't help.
-
Andree, a screenshot of the columns is in my original post.
-
Can you post the formula that you most recently tried with all of the extra parenthesis removed?
-
The commas broke the formula unfortunately. And even with the + instead of the comma, the last formula which I'm trying to add in won't work.
-
Right. You're putting in way too many parenthesis. Did you try it following the syntax I suggested? If so and that formula gave you an error, can you copy-paste it from the sheet to here?
Judging by your latest screenshot though, it looks like there is a column name misspelled. They should all be highlighted different colors (similar to your first screenshot) if they are being recognized by the formula.
-
Hi Paul, I have tried following the syntax you suggested and it comes up as unparseable:
=SUM(SUP@row * 250, LH@row * 175, FC@row * 150, [1/2C]@row * 75, [A/L]@row * 75, TOH@row * 125, OR@row * 443, ORS@row * 700, ORL@row * 550, [S/BY]@row * 298, SAL@row * 250, LAL@row * 131.25)
When using my original formula, it worked fine, but not when I was trying to add LAL@row * 131.25.
I want it to count each of the abbreviations in a line and multiply it by the figures in the formula above.
-
The syntax in your latest comment is fine. Instead of typing out the column names, click on the cell in the same row of each column when needed. It looks like there is an issue now with a column name not being recognized.
-
I need it to be able to count each of the abbreviations across the whole month (columns 1 to 31) not just certain columns/cells.
-
Ah. There's the problem. You need to be referencing cells. What you really need is a series of COUNTIFS added together.
=(COUNTIFS([1]@row:[31]@row, "SUP") * 250) + (COUNTIFS([1]@row:[31]@row, "LH") * 175)
-
Thanks Paul, you're amazing!!! I have been trying all different things for days but yours has worked!
-
Happy to help. 👍
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 461 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 462 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!