Formula Help!

Clare C

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!

Paul Newcome

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)

Andrée Starå

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!

Paul Newcome

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.

Clare C

Thanks Paul, gave that a go and it didn't help.

Clare C

Andree, a screenshot of the columns is in my original post.

Paul Newcome

Can you post the formula that you most recently tried with all of the extra parenthesis removed?

Clare C

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.

Paul Newcome

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.

Clare C

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.

Paul Newcome

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.

Clare C

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.

Paul Newcome

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)

Clare C

Thanks Paul, you're amazing!!! I have been trying all different things for days but yours has worked!

Paul Newcome

Happy to help. 👍