Named range automatically in formula

Copper Contributor

Hello,

I have a question about named ranges. 

 

In a raw Data sheet I have a column C with sentences in it, all different sentences. 
I also have in a 2nd sheet a matrix with a column A of words and a row 1 of other words (such as it creates a double entry table from A2/B1 to, say, L30 with empty cells in the middle) that I would like to match and count if these words appear in the sentences from col C.
The idea is to fill in the matrix by counting the number of times the words from column A and row 1 appears in all the sentences from col C combined.

The thing is that each cell from column A and row 1 need to have several distinct words. So I created a 3rd sheet with columns for each set of words I need in that cell, and gathering them into a named range (for each set of words).

Right now the formula that I have (and works) is: =SUMPRODUCT(COUNTIFS(Data!$AA:$AA,"*"&NamedRange1&"*",Data!$AA:$AA,"*"&NamedRange2&"*"))

(Data!$AA:$AA is the column with all the sentences)

My problem with this is that I have to type in the named range manually for every single cell.

My question is how to reference a named range in a formula such that i don't need to type in the named range manually in each cell? I would like to be able to double click (or Ctrl +D) so that each cell from column A and row 1 refers to the named range and counts automatically whether or not and how often the requested words appear in the sentence.


Thank you

0 Replies