COUNTA syntax error

[SMiller89]

I'm trying to create a SharePoint list that will retrieve the responses from a Form quiz and have 2 calculated columns for Total Score (COUNT) and Total Attempted (COUNTA) but I keep getting syntax errors for both. 

To make it simpler I made the columns A1-A50 (Answer) and S1-S50 (Score)

=COUNTA([A1],[A2],[A3],[A4],[A5],[A6],[A7],[A8],[A9],[A10],[A11],[A12],[A13],[A14],[A15],[A16],[A17],[A18],[A19],[A20],[A21],[A22],[A23],[A24],[A25],[A26],[A27],[A28],[A29],[A30],[A31],[A32],[A33],[A34],[A35],[A36],[A37],[A38],[A39],[A40],[A41],[A42],[A43],[A44],[A45],[A46],[A47],[A48],[A49],[A50])

=COUNT([S1],[S2],[S3],[S4],[S5],[S6],[S7],[S8],[S9],[S10],[S11],[S12],[S13],[S14],[S15],[S16],[S17],[S18],[S19],[S20],[S21],[S22],[S23],[S24],[S25],[S26],[S27],[S28],[S29],[S30],[S31],[S32],[S33],[S34],[S35],[S36],[S37],[S38],[S39],[S40],[S41],[S42],[S43],[S44],[S45],[S46],[S47],[S48],[S49],[S50])

I've tried the formulas with spaces after the commas, with semicolons instead, without the square brackets, with single quotes, with double quotes...I'm at a loss for what the error is.

[#2]

@SMiller89 you are getting the errors because you can't use COUNT or COUNTA in SharePoint calculated columns, they are Excel functions. In SharePoint you need to take a totally different approach and use several if statements and add them depending on the answer. So in this example we have 9 columns that are either Passed, Failed or blank "" because the question hasn't been attempted.

There are then calculated columns for questions answered, the number correct and the percentage correct.

The formula for answered is as follows and it means if check1 is blank then the score is 0 otherwise 1 PLUS etc etc:
 =IF(Check1="",0,1)+IF(Check2="",0,1)+IF(Check3="",0,1)+IF(Check4="",0,1)+IF(Check5="",0,1)+IF(Check6="",0,1)+IF(Check7="",0,1)+IF(Check8="",0,1)+IF(Check9="",0,1)

The formula for correct is:
=IF(Check1="Passed",1,0)+IF(Check2="Passed",1,0)+IF(Check3="Passed",1,0)+IF(Check4="Passed",1,0)+IF(Check5="Passed",1,0)+IF(Check6="Passed",1,0)+IF(Check7="Passed",1,0)+IF(Check8="Passed",1,0)+IF(Check9="Passed",1,0)

The formula for Percent is:
=Correct/9
with the show as a percentage checked.

Rob
Los Gallardos
Intranet, SharePoint and Power Platform Manager (and classic 1967 Morris Traveller driver)

[#3]

@RobElliott COUNT and COUNTA functions are available in SharePoint calculated formula as well.

@SMiller89 But, those might not work for your requirements. Check below uses of these functions and see if those will work for your requirements or not.

• COUNT function : Counts the number of arguments that contain numbers.
• COUNTA function : Counts the number of arguments that are not empty.

Example: 

N1, N2 & N3 columns are of type Number and T1, T2 & T3 columns are of type Text. CountN and CountT are calculated columns (formula used given in above image).

---

Please click Mark as Best Response & Like if my post helped you to solve your issue. This will help others to find the correct solution easily. It also closes the item. If the post was useful in other ways, please consider giving it Like.

[#4]

@ganeshsanap My answer columns are all set as text and the score columns are all set as numbers.

(but I've tried switching between COUNT and COUNTA just to troubleshoot)

I just tried COUNT with only S1-S10 and that worked, so I kept adding more columns until I got the syntax error again at S31. Is there just a limit on how many columns I can reference?

[#5 · Accepted solution]

@SMiller89 Yes, you can only add up to 30 arguments to single count function. However, you can use multiple COUNT function based on number of columns you are using.

For example:

=COUNT([T1],[T2],[T3],[T4],[T5) + COUNT([T6],[T7],[T8],[T9],[T10)

You can add up to 30 columns in each of the count function (I have just used 5 columns each to show you how to use multiple count functions).

---

Please click Mark as Best Response & Like if my post helped you to solve your issue. This will help others to find the correct solution easily. It also closes the item. If the post was useful in other ways, please consider giving it Like.