Get the Count only values available in Specific list

Question

I have specific list of names (ABC, BCD, ......., JKL), Then separately I am maintain the another location with those list of name plus another separate words (Like - Off, ON, .... etc)

So I need count then no of name mention only in that specific list.

You can get my example in below G sheet, also Feel Free to contact me for any clarification.

https://docs.google.com/spreadsheets/d/1bKA1KOs36ZDfR6VY18Md260MaFJWvO8uY3zto8jscOE/edit?usp=sharing

Indika

Best regards

Indika

Answer 1

try:

=INDEX(BYROW(XLOOKUP(D2:O11, A2:A, IFERROR(A2:A/0, 1),,,1), 
 LAMBDA(x, IFERROR(1/(1/SUM(x))))))