Basic Formula
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

Your formula is using COUNTIFS but you are trying to enter multiple ranges... COUNTIFS can only accept 1 range to count...
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.3K Get Help
 364 Global Discussions
 199 Industry Talk
 428 Announcements
 4.4K Ideas & Feature Requests
 136 Brandfolder
 127 Just for fun
 128 Community Job Board
 445 Show & Tell
 28 Member Spotlight
 1 SmartStories
 283 Events
 35 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!