Formula not being evaluated in COUNTIFS multiple criteria array

[Kai_GSE]

I'm using the following formula:

=IFERROR(IF(SUM(COUNTIFS(INDIRECT(CELL("address",INDEX(MuchAdoSceneAndCast,MATCH('Much Ado Schedule'!C5,MuchAdoSceneList,0),2))):INDIRECT(CELL("address",INDEX(MuchAdoSceneAndCast,MATCH('Much Ado Schedule'!C5,MuchAdoSceneList,0),17))),{"Full Cast",INDEX(MuchAdoCastCharacterMatch,MATCH($A$1,CastList,0),1),INDEX(MuchAdoCastCharacterMatch,MATCH($A$1,CastList,0),2),INDEX(MuchAdoCastCharacterMatch,MATCH($A$1,CastList,0),3)}))>0,'Much Ado Schedule'!C5,""),"0")

which I would like to condense to:

=IFERROR(IF(SUM(COUNTIFS($B$2:$R$2,{"Full Cast","Claudio",”0”, “0"}))>0,'Much Ado Schedule'!E10,""),"").

The $B$2:$R$2 portion works correctly, but the INDEX(MuchAdoCastCharacterMatch,MATCH($A$1,CastList,0),1) does not. Frustratingly, when I highlight it in the formula bar and press F9, it does indeed return "Claudio", but when I try to press enter, I get the "There's a problem with this formula" message. I don't get that if I put quotations, and write "INDEX(MuchAdoCastCharacterMatch,MATCH($A$1,CastList,0),1)", but then this reduces to ""Claudio"" with double quotations, and this doesn't match as I would like.

Any ideas why this is not compiling correctly?

PS It seems to work fine in COUNTIF, as opposed to COUNTIFS i.e. with only one criteria, and no curly brackets.

PPS As a fun side note, when I compile =INDIRECT(CELL("address",INDEX(MuchAdoSceneAndCast,MATCH('Much Ado Schedule'!C5,MuchAdoSceneList,0),2))), in it's own cell, it returns "Leonata" (which I wouldn't want), but when I use it in the top formula, it returns $B$2 exactly as I would like...this inconsistency is confusing me a lot!

Thanks in advance

 

[Kai_GSE]

Update:

It works perfectly if I separate into several COUNTIF functions, rather than trying to use COUNTIFS with an array for multiple criteria, but the function, is now much longer:

=IFERROR(IF(SUM(COUNTIF(INDIRECT(CELL("address",INDEX(MuchAdoSceneAndCast,MATCH('Much Ado Schedule'!C5,MuchAdoSceneList,0),2))):INDIRECT(CELL("address",INDEX(MuchAdoSceneAndCast,MATCH('Much Ado Schedule'!C5,MuchAdoSceneList,0),17))),"Full Cast"),COUNTIF(INDIRECT(CELL("address",INDEX(MuchAdoSceneAndCast,MATCH('Much Ado Schedule'!C5,MuchAdoSceneList,0),2))):INDIRECT(CELL("address",INDEX(MuchAdoSceneAndCast,MATCH('Much Ado Schedule'!C5,MuchAdoSceneList,0),17))),INDEX(MuchAdoCastCharacterMatch,MATCH($A$1,CastList,0),1)),COUNTIF(INDIRECT(CELL("address",INDEX(MuchAdoSceneAndCast,MATCH('Much Ado Schedule'!C5,MuchAdoSceneList,0),2))):INDIRECT(CELL("address",INDEX(MuchAdoSceneAndCast,MATCH('Much Ado Schedule'!C5,MuchAdoSceneList,0),17))),INDEX(MuchAdoCastCharacterMatch,MATCH($A$1,CastList,0),2)),COUNTIF(INDIRECT(CELL("address",INDEX(MuchAdoSceneAndCast,MATCH('Much Ado Schedule'!C5,MuchAdoSceneList,0),2))):INDIRECT(CELL("address",INDEX(MuchAdoSceneAndCast,MATCH('Much Ado Schedule'!C5,MuchAdoSceneList,0),17))),INDEX(MuchAdoCastCharacterMatch,MATCH($A$1,CastList,0),3)))>0,'Much Ado Schedule'!C5,""),"0")