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
Hi Julie
It would help if you can post a file with some sample data.
It will help others to understand your needs better.
I also prefer an example Julie! But I think your problem isn't a problem: when you use a named range, you can copy and paste your formula and the named range comes with it, so you don't have to type the name again! Or am I missing something?
Frans
