Countif formula across multiple sheets

 I am setting up a workbook for tracking some video game elements for a friend. It’s set up with a couple cover sheets that pull data from 100 discrete sheets that track per match data. For ease, these tabs for individual games are named just by numeral. So 1,2,3,4,5 etc.

The cover sheet I am using is providing totals from each individual sheet. For example, this particular one I’m working on checks to see if the character being used is Billy (there’s about a dozen that it totals seperately). The cell on each match page stays the same (B2). I have set up column z on my cover sheet to hold the sheet list for the formula, so z1-z100 just contains 1,2,3,4,5, etc.

This is the formula I’m using:

=SUMPRODUCT(COUNTIF(INDIRECT(“‘”&Z1:Z100&”‘!B2″),”billy”))

Which keeps popping up as the correct one to use for this idea. The problem is that it ONLY grabs the data from the first sheet (1), which is in z1. I feel like the range for the sheets is correct. Can’t figure out what the problem is.

 

This was presented as counting the number of times a name appeared in an array of 1000 random names on different sheets. Which part is causing the issue? The number of sheets?

By: Pete

Leave a Reply

Your email address will not be published. Required fields are marked *