Forum Discussion
track frequencies of keywords in Excel sheet
AnaStudent If you are open to a Power Query (PQ) solution, please find a small example attached. The key is to split the Keywords column (B) from the data table into separate keywords, then unpivot the table. The result is shown in columns D and E. This could then be the source for a regular pivot table, if you so prefer, or you perform that step within PQ. See output in G1:M7.
Hello how are you doing?
I hope I can help you.
Considering your Column A as you YEAR Column and Column B your keyword columns.
Lets say you put your Keywords criteria on G1 and the Year criteria on F2.
Use the following formula =COUNTIFS(A2:A3,F2,B2:B3,"*"&G1&"*")
As this formula is checking using wildcard, doesn't matter the position of the keyword, it will be find and the formula will calculate the number of occurrences for it.
Thank you for your answer.
The thing is I can't use COUNTIFS, because I have too many words and I can't fill in conditions manually.