Excel formula to calculate a SCORE based on text answers in cells

Question

Hello brain trust! I'm reaching out for help and guidance on the proper excel formula for a software requirement tracker, where I am evaluating a series of requirements and scoring each software platform to determine which one has the highest score (# of requirements met). Here's what it looks like:

1. I have a series of questions/requirements (one per row) on a sheet
2. I am then evaluating the response to each question in separate columns (one column per software platform)
   • Answers include: Yes, No, Sort of
3. Every YES answer = 1 point, Every No answer = 0 points, every Sort Of answer = .5 points
4. For each software vendor I want to calculate a score as a percentage value out of 100% (meaning they were yes's for every requirement listed on the spreadsheet).
   • So, if every response in the column was yes, they would have a score of 100% (300 points = (1*300/300))
   • If there were 285 yes's, 10 no's, and 5 sort of's, they would have a score of 95% (287.5 points = ((1*285)+(0*10)+(5*.5))/300)

I tried something like this, but clearly it's wrong:

=(IF(B4:B300="yes",1) + if(B4:B300="no",0,0) + if(B4:B300="sort of",0.5,0))/count(B4:B300)

I know I'm missing a critical element here to get the point value assigned, but then also do the math to generate the percentage.

Note: Not every row in the spreadsheet will have a value (or should count toward the total answer calculation) as some rows will be section headings

Your help and guidance is very much appreciated! Thanks!

Answer 1

You can use

=(COUNTIF(C6:C87,"Yes")+(COUNTIF(C6:C87,"Sort Of")/2))/29

since your sample workbook contains 29 questions, or

=(COUNTIF(64:C87,"Yes")+(COUNTIF(C6:C87,"Sort Of")/2))/(COUNTA($A$6:$A$24)+COUNTA($A$26)+COUNTA($A$28:$A$36))

using COUNTA instead of COUNT, since COUNT only counts number values, or

=(COUNTIF(C6:C87,"Yes")+(COUNTIF(C6:C87,"Sort Of")/2))/COUNTIFS($A$6:$A$87,"<>Section*",$A$6:$A$87,"<>")

Answer 2

=(COUNTIF(B4:B300,"Yes")+COUNTIF(B4:B300,"Sort Of")/2)/297

(B4:B300 contains 297 cells, not 300)

or if you only want to count questions that have been answered

=(COUNTIF(B4:B300,"Yes")+COUNTIF(B4:B300,"Sort Of")/2)/COUNTA(B4:B300)

Answer 3

Hi Hans. Thank you for the prompt reply. I tried the formula, but ran into an issue where the Nos were not being calculated into the percentage total. It returned a 100% score even though there were several responses that were "No".

Answer 4

I don't see how that could happen, Could you create a stripped-down copy of the workbook demonstrating the problem (without sensitive information) and make it available through one of the websites that let you upload and share a file, such as OneDrive, Google Drive, FileDropper or DropBox. Then post a link to the uploaded and shared file here.

Answer 5

No problem. Here you go: https://docs.google.com/spreadsheets/d/1-ZNst1KQ0KFIgC0bBVPQy8e9ReAAFe3XCUYhKGNOI7Y/edit?usp=sharing

I noticed when I recopied and pasted I now am getting different results, though not sure why. However, there's one thing I would kindly ask for your additional help in properly updating which is, I want it to count ALL cells in the column (where an answer would be provided via dropdown, not the section headers) as part of the equation so that it's not just calculating against the cells that are completed.

Right now, it's set to CountA, which only calculates the formula based on whether the field has been completed. The score should be reflected against the entire list of requirements. If the user has answered only 10 of them and they're all yes, it should not show 100%, since they technically haven't hit a 100% score given there are outstanding items still left to answer. I set that example up for Column C, Software 2. The expected outcome of this score would be 34% (10 Yes's divided by / 29 total requirements to measure). I tried looking up a way to count cells for each section, regardless of their response (as example in Column D), but it's now throwing an error related to the divide by 2 parameter.

Thank you again for your help and patience :)