CODEWITHSUNDEEP
excelcountif
westman2222
westman2222

Reputation: 683

Consolidate Excel formula to COUNTIF all tabs

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")

Upvotes: 0

Views: 170

Answers (1)

tigeravatar
tigeravatar

Reputation: 26640

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

tigeravatar example for westman2222

In cell E2 is this formula:

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

Upvotes: 2

Related Questions