Match and CountIF Across Multiple Sheets Using Formula

[Excel Fox]

Make a list of all the required sheets in a sheet, and give the range a name > List

Alternatively, instead of creating a list by given a range reference, you could also do so like this ={"Date1","Date2","Date3"}

Then, in the summary sheet, in B14, use

=SUMPRODUCT(COUNTIF(INDIRECT("'"&ListAlternate&"'! "&ADDRESS(MATCH(B$13,INDIRECT("Date1!$I$1:$I$14"), 0),MATCH($A14,Date1!$A$2:$H$2,0))),"yes"))

and drag down.

Check the attachment