COUNTA alternative in Smartsheet
Hello,
I'm trying to recreate/transfer a salary/reward solution in Excel over to Smartsheet and two of the columns have a rather complicated formula, which has a function I don't believe is available in Smartsheet (COUNTA). When I've tried to replicate it I get an error message (UNPARSEABLE) The formulas are:
=IF(SUM(AR13:AT13)>0,IFS(OR(AU13="",AV13=""),"Please enter review reason and detailed explanation",COUNTA(AR13:AT13)>1,"Please enter one type of increase recommendation only",AR13<>"",ROUND(AR13*V13,0),AS13<>"",ROUND(AS13*U13,0),AT13<>"",ROUND((AT13AL13)*U13,0)),0)
=IF(SUM(AR13:AT13)>0,IFS(OR(AU13="",AV13=""),"Please enter review reason and detailed explanation",COUNTA(AR13:AT13)>1,"Please enter one type of increase recommendation only",AW13<>"",AW13/V13),0).
I'm wondering if anyone knows what other function I could use to get these work in Smartsheet. I did try COUNTIF, but it didn't work either.
For reference, this is what they look like Excel  they should bring up a salary increase numerically and a percentage respectively.
Best Answers

You are correct in that COUNTA is not present in Smartsheet. You can use COUNTIF, as you mentioned, though I prefer to use COUNTIFS instead. There is also no IFS, so to cover multiple cases, we must nest several IF statements. Will you be setting these up as column formulas? If so, here is the equivalent of the first formula. Since Smartsheet references column names instead of alphabetic references, you will have to replace the column references with your actual column names.
=IF(SUM([AR]@row:[AT]@row) > 0, IF(OR(ISBLANK([AU]@row), ISBLANK([AV]@row)), "Please enter review reason and detailed explanation", IF(COUNTIFS([AR]@row:[AT]@row, NOT(ISBLANK(@cell))) > 1, "Please enter one type of increase recommendation only", IF(NOT(ISBLANK([AR]@row)), ROUND([AR]@row * [V]@row, 0), IF(NOT(ISBLANK([AS]@row)), ROUND([AS]@row * [U]@row, 0), IF(NOT(ISBLANK([AT]@row)), ROUND(([AT]@row  [AL]@row) * [U]@row, 0)))))), 0)

You are dead on with the concept... you only missed it with parentheses.
=IF(SUM([AR]@row:[AT]@row) > 0, IF(OR(ISBLANK([AU]@row), ISBLANK([AV]@row)), "Please enter review reason and detailed explanation", IF( COUNTIFS([AR]@row:[AT]@row, NOT(ISBLANK(@cell))) > 1, "Please enter one type of increase recommendation only", IF(NOT(ISBLANK([AW]@row)), [AW]@row / [V]@row))), 0)
Answers

You are correct in that COUNTA is not present in Smartsheet. You can use COUNTIF, as you mentioned, though I prefer to use COUNTIFS instead. There is also no IFS, so to cover multiple cases, we must nest several IF statements. Will you be setting these up as column formulas? If so, here is the equivalent of the first formula. Since Smartsheet references column names instead of alphabetic references, you will have to replace the column references with your actual column names.
=IF(SUM([AR]@row:[AT]@row) > 0, IF(OR(ISBLANK([AU]@row), ISBLANK([AV]@row)), "Please enter review reason and detailed explanation", IF(COUNTIFS([AR]@row:[AT]@row, NOT(ISBLANK(@cell))) > 1, "Please enter one type of increase recommendation only", IF(NOT(ISBLANK([AR]@row)), ROUND([AR]@row * [V]@row, 0), IF(NOT(ISBLANK([AS]@row)), ROUND([AS]@row * [U]@row, 0), IF(NOT(ISBLANK([AT]@row)), ROUND(([AT]@row  [AL]@row) * [U]@row, 0)))))), 0)

Thanks for coming back to me so quickly on this and helping out Carson  much appreciated. I will give this a go later and come back to you to advise on progress.

Hi Carson that's worked perfectly when I've added in the column headers for the first formula. Thank you so much!
I'm trying to apply the same principle for the second formula, but it's advising me INCORRECT ARGUMENT SET. I've done it as follows (obviously changing the alphabetic references for the column headers in Smartsheet):
=IF(SUM([AR]@row:[AT]@row) > 0, IF(OR(ISBLANK([AU]@row), ISBLANK([AV]@row)), "Please enter review reason and detailed explanation", IF(COUNTIFS([AR]@row:[AT]@row, NOT(ISBLANK(@cell))) > 1, "Please enter one type of increase recommendation only", IF(NOT(ISBLANK([AW]@row)), [AW]@row)/[V]@row), 0)
I may have over simplified it by removing some key elements?

You are dead on with the concept... you only missed it with parentheses.
=IF(SUM([AR]@row:[AT]@row) > 0, IF(OR(ISBLANK([AU]@row), ISBLANK([AV]@row)), "Please enter review reason and detailed explanation", IF( COUNTIFS([AR]@row:[AT]@row, NOT(ISBLANK(@cell))) > 1, "Please enter one type of increase recommendation only", IF(NOT(ISBLANK([AW]@row)), [AW]@row / [V]@row))), 0)

Carson you're a legend  thank you so much for your assistance with this. I really appreciate it. This has worked perfectly!

I'm glad it worked for you! 👍
Help Article Resources
Categories
Check out the Formula Handbook template!