What is wrong with my excel 365?

[Perry Cheng]

So, I am trying to use either VLOOKUP or simple Countif and if I have an array of list, it only look up the first on the list? What am I doing wrong?

=COUNTIF(B2,$E$2:$E$3)

Column B	Column C	Column D	Column E
Tom	=COUNTIF(B2,$E$2:$E$3)​		Mary
Mary	0​		John
Jane	0​
Jack	0​
John	0​

 

[Perry Cheng]

OK, I think my thought process may be reversed. The range look up list in Column E should have go first!?? Weired)

 

[Fluff]

Hi & welcome to MrExcel.
Your countif would need to be

=SUM(COUNTIF(B3,$E$2:$E$3))

or another option

=ISNUMBER(XMATCH(B2,$E$2:$E$3))+0

 

[jasonb75]

With 365, try

=COUNTIF(E2:E3,B2:B6)

Manexcel.xlsx
	B	C	D	E
2	Tom	0		Mary
3	Mary	1		John
4	Jane	0
5	Jack	0
6	John	1
Sheet4

Cell Formulas
Range	Formula
C2:C6	C2	=COUNTIF(E2:E3,B2:B6)
Dynamic array formulas.

 

[Perry Cheng]

Jason, so actually I am trying to figure out something like this, but has been struggling.

B	C	D (List)
Tom, Mary, Jane	=COUNTIF VALUES IN THE LIST IS IN COLUMN B?	Mary
John, Edith, Karen		John
Mike, Cecil, Jane

This should not be that difficult. I must be have a brain freeze moment.

I tried, =COUNTIF(B2:B6, "*"&D2:D3&"*") but it fails. WHY??

 

[Fluff]

How about

+Fluff 1.xlsm
	B	C	D
1	B	C	D (List)
2	Tom, Mary, Jane	1	Mary
3	John, Edith, Karen	1	John
4	Mike, Cecil, Jane	0
5	Tom, Mary, John	2
Master

Cell Formulas
Range	Formula
C2:C5	C2	=SUM(COUNTIFS(B2,"*"&$D$2:$D$3&"*"))

 

[Perry Cheng]

See I did exactly what you did and my excel is giving me all "0"

=SUM(COUNTIFS(B2,"*"&$D$2:$D$3&"*"))

 

[Perry Cheng]

I am wondering if there is something wrong with my excel program?? Very strange.

 

[Perry Cheng]

I think the problem I am discovering is the Range thing is not working. If it is a single cell comparison, it works.

If it were just "*"&$D$2&"*" it works, but when I do "*"&$D$2:$D$3&"*" it fails.

 

[Fluff]

Check that you don't have any leading/trailing spaces or other hidden characters in d2:d3