Count and VLookup Across Multiple Sheets

Hi.

I have an Excel (2016) file with sheets for every day of this month, containing an table of info on each sheet, in the same location - just the staff member names in the first column are different. (I've attached a sample spreadsheet with a few names on it.)

On a "Summary" sheet, I want to look up a staff member's name, search across all of the daily sheets (labelled "1 Mar", "2 Mar", "3 Mar" etc...) and provide a count IF column 2 on each table contains a 1 (col 2 will either be 1 or 0.)

I've given the sheets a Name of "lookup_sheets" and I'm currently using the following formula to do this:

=VLOOKUP(B5,INDIRECT("'"&INDEX(lookup_sheets,MATCH(1,--(COUNTIF(INDIRECT("'"&lookup_sheets&"'!$b$3:$d$10"),B5)>0),0))&"'!$b$3:$d$10"),2,FALSE)

However, with the first name on the list I'm getting a result of 1 but it should be 3 because although the name appears on each of the sheets there is only a "1" on three of them... Clearly I've gone wrong somewhere with the formula but I can't see where! Can anyone help? (Or even suggest a better/easier way to do this!)

Hi Glenn.

You are right! Two is correct - I was messing about with some other methods and forgot I added a 1 to the end of one of the Adam Ganee entries!

Your solution works perfectly, so thank you for your help; it's much appreciated

Hi Glenn.

I did both of those things just after posting #3. If this hasn't registered I must be having some technical issues today (especially with my earlier double post issue!) That's what I get for doing this during work time on outdated browsers!