Domenic wrote:
The following formula...

=SUMPRODUCT(--ISNUMBER(MATCH(G8:G14,pl_Resources,0)),--ISNUMBER(1/(LOOKUP
(G8:G14,pl_Provider)="Yes")),M8:M14)

...seems to fail when any one of the array of lookup values returns a
#N/A value.

[...]

An #N/A cannot go thru the ISNUMBER tests that figure in the formula.

Here is an example intermediate state of evaluation:

=SUMPRODUCT(--ISNUMBER({#N/A;2;6;#N/A;3;#N/A;#N/A}),--ISNUMBER(1/({#N/A;"No";"Yes";#N/A;"No";#N/A;"No"}="Yes")),{5;6;4;7;8;9;6})