NEW TO SMARTSHEETS!! Needing advice on creating a formula for N/A answers not to count
Two different equations are being used.
Equation 1: =IFERROR((SUM([RN Section 1 Score]@row, [RN Section 2 Score]@row, [RN Section 3 Score]@row, [RN Section 4 Score]@row)) / SUM(IF([RN Section 1 Score]@row = "", 0, 0.3), IF([RN Section 2 Score]@row = "", 0, 0.3), IF([RN Section 3 Score]@row = "", 0, 0.2), IF([RN Section 4 Score]@row = "", 0, 0.2)), "%")
Equation 2: =SUM([MD Section 1 Score]@row, [MD Section 2 Score]@row, [MD Section 3 Score]@row, [MD Section 4 Score]@row)
Equation 1 is used to obtain the average of a weighted score for 4 section. Each section is weighted differently.
Equation 2 is used to obtain the average of 4 sections of equal weights for the 4 sections of a different set of data.
Section 4 in each set of data may or may not be used depending on the type of information collected. If not relevant, the auto populated answer is "N/A". I need a formula that will populate an average, excluding section 4 if it is not used, and any N/A's throughout the data.
I have tried this formula: =COUNTIFS([MD Section 1 Score]@row, [MD Section 2 Score]@row, [MD Section 3 Score]@row, [MD Section 4 Score]@row), "Met") / COUNTIFS([MD Section 1 Score]@row, [MD Section 2 Score]@row, [MD Section 3 Score]@row, [MD Section 4 Score]@row), NOT([MD Section 1 Score]@row, [MD Section 2 Score]@row, [MD Section 3 Score]@row, [MD Section 4 Score]@row), = "N/A")), however it returns a syntax error.
Answers

Hey @S. Roach
The original formula does not have the syntax smartsheet is expecting. Correcting the syntax however will not correct the formula logic.
I will make an assumption that your columns are either contiguous or you don't have other columns in between that have a response of 'Met' or 'N/A'. If my assumption is incorrect please let me know.
=COUNTIFS([MD Section 1 Score]@row:[MD Section 4 Score]@row), "Met") / COUNTIFS([MD Section 1 Score]@row:[MD Section 4 Score]@row), <>"N/A")
Will this formula work for you?
Kelly

Hi Kelly!
Thank you for the response. Each section has a set of questions (columns) that will be Met, Not Met, N/A. The auto fill for these columns is N/A. And then the total for each section is weight at 25%. Depending on the audit being done, section 4 may be completely N/A, but all other sections are either Met, or N/A. When section 4 is N/A, these audits are coming back 80% instead of 100%
Section 1= column headers "1.1 MD, 1.2 MD, 1.3 MD"
Section 2= column headers "2.1 MD>2.18 MD" (each column is the respective number 2.12.18
Section 3= column headers "3.1 MD 3.3 MD"
Section 4= column headers "4.1 MD4.3 MD"
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.4K Get Help
 364 Global Discussions
 200 Industry Talk
 430 Announcements
 4.4K Ideas & Feature Requests
 137 Brandfolder
 129 Just for fun
 128 Community Job Board
 446 Show & Tell
 28 Member Spotlight
 1 SmartStories
 284 Events
 35 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!