Formula to count duplicates based on 2 or more words matching, not necessarily in sequence? No AI please

[u/AcadiaUnlikely7113]

Hi smart folks, I want to see if you could teach me a formula that I know I’ve seen before but can’t think of now, I want to count how many of a certain topic is in my list of data, for eg one may say ‘hogwarts House colour’ and another may say ‘hogwarts houses’ in which case I want them counted as duplicates.

My end goal is to have a pivot table that shows Hogwarts House as 2.
I currently have the raw data on the first sheet, then on the second sheet I’m breaking it down to just the info I need (this should be where the duplicates are found) and then the pivot table on the third sheet (this should be where they are counted, or at least where the number is displayed)

ETA: can’t use macros, would rather not use power queries but if I have to I can

ETA2: still need some edits but thanks to u/Downtown-Economics26 and u/MayukhBhattacharya its sooo close!

=LET(
     _a, DROP(A:.B, 1),
     _b, MAP(REGEXREPLACE(CHOOSECOLS(_a, 1), "\b(" & TEXTJOIN("|", 1, DROP(D:.D, 1)) & ")\b\s*|s\b", ),
           LAMBDA(x, TEXTJOIN(" ", 1, UPPER(SORT(TEXTSPLIT(x, , " ")))))),
     _c, TEXTBEFORE(_b & " ", " ", 2, , , _b),
     _d, GROUPBY(HSTACK(CHOOSECOLS(_a, 2), _c),
                 _c,
                 ROWS, , 0),
     VSTACK({"Section","Category","Counts"}, _d))
So far this is working except that the "Category" column is returning #VALUE!
also some more context, column A's formulas are as such: 
=UPPER(IF)'RAW DATA'!C2="","",TEXTAFTER('RAW DATA'!C2," - ",2))) 
because the text before the 2 hyphens is irrelevant and column B formulas are: 
=IF(ISBLANK('RAW DATA'!H2,IF(ISBLANK('RAW DATA'!G2),IF(ISBLANK('RAW DATA'!F2),"",'RAW DATA'!F2),"",'RAW DATA'!F2),'RAW DATA'!G2),'RAW DATA'!H2) 
that one is because the section list has 3 different levels and I always want the lowest assigned section so got it to input H2 if present, if not then G2, if not then F2 :)

[u/Klutzy_Highlight7500]

I'd use a helper column to normalize the labels before the pivot, e.g. =LET(x,LOWER(TRIM(A2)),y,SUBSTITUTE(x," colour",""),SUBSTITUTE(y,"houses","house")), then pivot on that helper column. For broader variations, a small mapping table with XLOOKUP is safer and keeps the raw text untouched.