Consolidate Excel formula to COUNTIF all tabs

Question

I am currently using a COUNTIF formula to count the word "Competitor" across multiple sheets. Is there a good way to consolidate this formula to run COUNTIF for a range of sheets?

 =COUNTIF('Outside 12.2'!P2:P4977,"Competitor")+COUNTIF('Outside 12.7'!P2:P4977,"Competitor")+COUNTIF('Outside 12.8'!P2:P4977,"Competitor")+COUNTIF('Outside 12.12'!P2:P4977,"Competitor")+COUNTIF('Outside 12.19'!P2:P4977,"Competitor")+COUNTIF('Big East 11.30'!P2:P4977,"Competitor")+COUNTIF('Northern Tier 11.25'!P2:P200,"Competitor")+COUNTIF('Northern Tier 11.23'!P2:P56,"Competitor")+COUNTIF('Northern Tier 1'!P2:P1000,"Competitor")

Answer 1

On a sheet where you are making the calculation, setup a table like this:

In cell E2 is this formula:

=SUMPRODUCT(COUNTIF(INDIRECT("'"&A2:A10&"'!"&B2:B10),E1))