named range referenced in formula

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

Can you attach a sample workbook showing what you want as your expected output.

Go Advanced -> Manage Attachments -> Upload

Hi kersplash,

The first screenshot is the sample matrix translated in english.
The actual matrix is in japanese, where the same word can have different spelling (which is why i need this named range).
Basically i want to count the number of sentences that has this one body part A with this one symptom B,
repeating this with however many body parts and symptoms there are.

Hope this helps,

Oh also this is the named ranges i have defined in a different sheet.
E.g. cells G2 to G4 are under 1 named range, called Tongue.

You'll need to mock up the English version (for me anyway) into a sample workbook and attach it (not images) showing your expected output for your sample.

here you go
thank you for your quick replies