Creating a conditional formula to summarize row data meeting certain requirements

Cindy H

New to SmartSheet and trying to learn the syntax for a formula I want to enter. I have a sheet with multiple columns to collect data on the health of an organization. I created a column for Risk Level and want to add up how many of these risk factors exist. For example, if ReserveFund is “No”, then add one to the risk level, if OmissionsErrors is “Yes” then add one to the risk level. I want the formula to work across the rows for each organization, looking at data in 11 columns.

I tried: if([ReserveFund] @row = "No", 1,0) + if([OmissionsErrors] @row = "Yes",1,0), etc, and that was incorrect (“#unparseable”).

I also tried using the AI formula generation and struck out. Other ideas?
Thank you in advance!

Paul Newcome

@John_Foster You can add multiple IFs together like this. I do it frequently.

@Cindy H The issue is going to be the space before each @row. Try removing that so you have

[ColumnName]@row

If this doesn't work, can you provide a screenshot of the formula open in the sheet as if you are about to edit it?

John_Foster

Hi @Cindy H,

I am not sure that you can do multiple if's in the same syntax and then add the outcomes. You can add helper columns that return the 1 or 0 for each of the options and then SUM those columns.

Thanks,
John

Paul Newcome

@John_Foster You can add multiple IFs together like this. I do it frequently.

@Cindy H The issue is going to be the space before each @row. Try removing that so you have

[ColumnName]@row

If this doesn't work, can you provide a screenshot of the formula open in the sheet as if you are about to edit it?

John_Foster

@Paul Newcome Thanks Paul. It was odd, I used an existing sheet I have to test the formula and could not get it to work, I have just refreshed the sheet and it is now working without any further changes.

Cindy H

@Paul Newcome Thank you!!! Such a simple fix - and now my formula is working as intended! Appreciate you taking the time to help! :)

@John_Foster - Thank you as well for chiming in! Creating the individual helper columns wasn't my first choice, but that definitely would have worked. :)

Paul Newcome

Happy to help. 👍️