Write once a word written several times in another table and find a percentage by category

Seems like one easy way to accomplish this would be to add two columns, Good and Bad.
The columns are populated using formulas similar to: =IF([@[good / bad]]=Table1[[#Headers],[Good]],1,0)
The results are displayed using a pivot table to which the following calculated field is added: =Good/(Good+Bad )
Note that when new items are added to Table1 (columns A:E) the pivot table needs to be refreshed.
Let us know if you have any questions.

If you don't want to add columns and since you are using the 365 version of Excel, you could add Table 1 to the data model and utilize the following measure:

Formula:

Please Login or Register  to view this content.

Pivot Table still needs to be refreshed after new data is added to Table1.
Let us know if you have any questions.

This:

Formula:

Please Login or Register  to view this content.

dragged down

Could be this (maybe):

Formula:

Please Login or Register  to view this content.

NOT dragged down (should spill)

@TMS'S You are right TQVM , almost forgot the most basic 365, SPILL features, 365 is still new to me (since July), Still discussing and groping
Add REP. to you. But the Forum system says it can’t give the same person

@wk9128: You're welcome.

The formula in cell F3 is a dynamic array formula using the UNIQUE function. That will spill down an unknown number of rows. Using F3# allows you to refer to the whole spilled range, regardless of how many rows there are. Effectively, it makes the formula in G3 dynamic … so you don't need to drag the formula down.

If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.