Hello trying to use this excel formula in smarsheet but getting unparsable error . can anyone help .

stuti.bhagwati

=IF(COUNTIFS(J:J, J2, F:F, "In Progress") + COUNTIFS(J:J, J2, F:F, "Not Started") > 0, "In Progress","Completed")

How do we specify a column range ?

=Chris Palmer

Hello,
One of the primary syntax differences from Excel is that Smartsheet requires the column name in brackets when writing formulas:
[Column Name]:[Column Name] for columns
[Column Name]@row for cells

Like so:

=IF(COUNTIFS([Column Name]:[Column Name], [Column Name]@row, [Column Name]:[Column Name], "In Progress") + COUNTIFS([Column Name]:[Column Name], [Column Name]@row, [Column Name]:[Column Name], "Not Started") > 0, "In Progress","Completed")

=Chris Palmer

Hello,
One of the primary syntax differences from Excel is that Smartsheet requires the column name in brackets when writing formulas:
[Column Name]:[Column Name] for columns
[Column Name]@row for cells

Like so:

=IF(COUNTIFS([Column Name]:[Column Name], [Column Name]@row, [Column Name]:[Column Name], "In Progress") + COUNTIFS([Column Name]:[Column Name], [Column Name]@row, [Column Name]:[Column Name], "Not Started") > 0, "In Progress","Completed")

stuti.bhagwati

Thank you so much for the prompt help. That worked !

i have another formula with trim - used your tip but still getting unparsabeale.

=IF(TRIM[Rack Location]@row<>"",IF(COUNTIFS([Rack Location]:[Rack Location], [Rack Location]@row, [Infra CI Readiness]:[Infra CI Readiness], "<>Completed")=0, "Ready to Move", "In Progress"),"")

=Chris Palmer

https://community.smartsheet.com/discussion/comment/483172#Comment_483172

Awesome! Happy that worked for you.

Smartsheet doesn't support =TRIM
You may have to explore alternative methods like =SUBSTITUTE like so:

=IF(SUBSTITUTE([Rack Location]@row, " ", "") <> "", IF(COUNTIFS([Rack Location]:[Rack Location], [Rack Location]@row, [Infra CI Readiness]:[Infra CI Readiness], "<>Completed") = 0, "Ready to Move", "In Progress"), "")

Or experiment with LEN() + LEFT() + MID() logic.
ChatGPT may be able to assist you more than I can with this route to have more specifics with your use case.

stuti.bhagwati

Hi Chris - the substitute formula worked but the whole smartsheet is hanging now. We have 7000 rows. Is this a heavy formula and thats the reason?

=Chris Palmer

Having a high row/column count can cause slow load times in Smartsheet with complex formulas.

If the delay is unbearable here's a few possible workarounds:

Option 1:
Insert a new row on the top of the sheet where your formula can be preserved.
Then paste values on the rest of the column with hardcoded data. Then use the formula from row 1 on an as needed basis.

Option 2:
Explore having a different sheet entirely that is dedicated to this formula, and use unique identifiers with an INDEX MATCH to pull the results of the formula back into your primary sheet.

Option 3:
Create a report of the primary sheet and use it for viewing purposes.

stuti.bhagwati

Keep getting this after trying your options also

=Chris Palmer

By hitting reference limits you may need to see if your license supports https://www.smartsheet.com/marketplace/premium-apps/datamesh

Or you use an API token with someone who knows a programming language like Python for a workaround.
Also consider asking ChatGPT for other possible solutions.

Unfortunately Smartsheet does have it's limitations.
It's a give and take with resources when comparing formula solutions between Excel and Smartsheet.