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.
- I am doing well. How about you?
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.
