COUNTA alternative in Smartsheet

SteCoxy

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((AT13-AL13)*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.

Carson Penticuff

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)

Carson Penticuff

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 Penticuff

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)

SteCoxy

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.

SteCoxy

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?

Carson Penticuff

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)

SteCoxy

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

Carson Penticuff

I'm glad it worked for you! 👍