Help creating a formula to replace text with a number and tally results, compare with another and determine which has more points

Please note that our forum is a public platform, and we will modify your question to hide your personal information in the description. Kindly ensure that you hide any personal or organizational information the next time you post an error or other details to protect personal data.
Hi RWHM

Thank you for reaching out to the Microsoft Q&A forum and for sharing your scenario.

Based on the information you provided, I tested this setup in my environment, and you can follow the steps below to see if the results match what you are looking to achieve.

1.Highlight users who did not complete both publishers

-Go to Home > Conditional Formatting > New Rule > Format cells where a formula is true.

-Enter the following formula: =OR( COUNTIFS($D:$D,$D2,$H:$H,"Carnegie")=0, COUNTIFS($D:$D,$D2,$H:$H,"Curriculum")=0 )

-Select “Stop if true”, choose a fill color (for example, red), and apply the rule to your data range.

This will highlight users who only submitted one publisher response or did not complete both.

2.Convert responses to numeric values (0, 1, or 2)

If responses include text such as “2 = Strong” or “1 = Adequate”, create helper columns and use: =VALUE(LEFT(cell,1))

3.Calculate total score per submission

Add a “Total score” column and use a SUM formula across the numeric response columns for each row.

4.Compare total scores by publisher for each user

Create a small summary section in the same sheet and list each email once (copy the Email column and use Data > Remove duplicates).

Then create columns to calculate totals for each publisher:

-Carnegie total: =SUMPRODUCT(($D$2:$D$11=D15)(ISNUMBER(SEARCH("Carnegie",$H$2:$H$11)))($Y$15:$Y$24))

-Curriculum total: =SUMPRODUCT(($D$2:$D$11=D15)(ISNUMBER(SEARCH("Curriculum",$H$2:$H$11)))($Y$15:$Y$24))

-Winner: =IF(E15>F15,"Carnegie", IF(F15>E15,"Curriculum","Tie"))

These formulas total the scores each user assigned to both publishers and determine which one received the higher score.

5.Count overall selections

To quickly see which publisher was selected more overall, use:

="Carnegie selected: "&COUNTIF(G15,"Carnegie")

="Curriculum selected: "&COUNTIF(G15,"Curriculum")

This will display the total number of users who selected each publisher.

I hope this helps.

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".  

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.