Home > Software engineering >  Get the Count only values available in Specific list
Get the Count only values available in Specific list

Time:11-20

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.

Sample

Best regards

Indika

CodePudding user response:

Here's a possible solution (I placed it in cell R2):

=ArrayFormula(BYROW(D2:O,LAMBDA(r,IF(COUNTBLANK(r)=COLUMNS(r),,
SUM(--REGEXMATCH(r,"\b"&TEXTJOIN("\b|\b",1,A2:A11)&"\b"))))))

CodePudding user response:

try:

=INDEX(BYROW(IF(""=IFNA(VLOOKUP(D2:O11, A2:A, 1, )), 0, 1), 
 LAMBDA(x, IFERROR(1/(1/SUM(x))))))

enter image description here

CodePudding user response:

In Office 365 you could use: =BYROW(D2:O11,LAMBDA(x,SUM(--(ISNUMBER(MATCH(x,$A$2:$A$11,0)))))) enter image description here

In older versions: =SUM(--(ISNUMBER(MATCH(D2:O2,$A$2:$A$11,0))))

enter image description here

CodePudding user response:

try:

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

enter image description here

  • Related