Wondering whether this is what you want:

• lookup and store values in values
• for each cell in M5:M500 get the sum by multiplying
  • 0 if hidden SUBTOTAL(103, m)
  • match words
  • values

This requires Excel for Microsoft 365. Please let me know if you're on an older version.

=LET(
    words, {"KE", "BF", "LF", "SOP", "ME", "ME+", "PS"},
    values, HLOOKUP(
        "Analytics Data",
        SIZING!$A$1:$D$8,
        MATCH({"KE", "BF", "LF", "SOP", "ME", "ME+", "PS"}, SIZING!$A$1:$A$8, 0),
        FALSE
    ),
    SUM(MAP(M5:M500, LAMBDA(m, SUM(SUBTOTAL(103, m) * (m = words) * values))))
)