Countifs in Excel with list of names as criteria

Question

I'm trying to write a countifs formula in Excel using a list of names. The following example illustrates what I would like to accomplish.

Of the members of the Beatles, who said yes? The syntax of the section in bold is where the problem lies.

=COUNTIFS(A1:A9,"YES",B1:B9,{"JOHN","GEORGE","RINGO","PAUL"})

Put differently, I'm trying to "manually select" the names of people who potentially said yes.

Answer 1

If you wrap your attempted formula in SUM function you should get the required answer, i.e.

=SUM(COUNTIFS(A1:A9,"YES",B1:B9,{"JOHN","GEORGE","RINGO","PAUL"}))

That works because the original formula returns an array of 4 results (one for each name) and you need the sum of those