That's about as clear as I make what I'm trying to do in Google Sheets.
Here's a sample table with two tabs.
Tab 1
Basically, I've got a table fed from a form. One column has names of say 50 various people. The names repeat randomly.
Other columns contain comments that each person made.
Tab 2
Here is essentially a heat map of keywords used by the different people. Column A are the keywords, while row 1 contains the keywords I'm interested in.
Each cell in this grid should (1) search tab 1 for all instances of each name, then (2) count the number of times the keyword appears in all of that person's comments.
Countifs doesn't work because the array arguments are different sizes.
I can't figure out how to phrase a filter embedded in a countif.
And using QUERY seems like it will cause trouble because my actual spreadsheet is something like 100 names and 40 keywords.
I'm open to suggestions and grateful for your help!
ken.
In B2 try
=sum(ArrayFormula(--regexmatch(filter(Sheet1!$B$2:$F, Sheet1!$A$2:$A=B$1), "\b"&$A2&"\b")))
Then fill down to the right and down as far as needed.
To make the match case-insensitive, change "\b"&$A2&"\b" to "\b(?i)"&$A2&"\b"
See if that works?