COUNTIF Across Multiple Worksheets

[excelbytes]

I have 12 worksheets, each with 40 columns and 87 rows, In each cell in those worksheets can appear a "P". I want to create a formula on a separate worksheet that I can drag across and down that will count the number of times "P" appears in that cell across all 12 worksheets. I've names the list of 12 worksheets "Sheets". I've seen several examples using SUMPRODUCT, COUNTIF, & INDIRECT, but can't quite come up with the combination that I can drag across and down to count correctly.

Thanks in advance for your help.

 

[jasonb75]

Try

=SUMPRODUCT(COUNTIF(INDIRECT("'"&Sheets&"'!RC",0),"P"))

 

[excelbytes]

I get a #REF! error. Can you explain the RC? Also, I'm not clear on the second argument for the INDIRECT function of 0.

 

[jasonb75]

Have you created the named range, 'Sheets' referring to a list of sheet names (as implied in your first post)? #REF! would suggest that either the named range 'Sheets' or 1 (or more) of the sheets listed in the range, 'Sheets' doesn't exist.

The second argument of indirect is either true or false, true is the default for normal references, false is for R1C1 references. RC is an R1C1 reference that gives the same cell address as the one holding the formula regardless of where it is on the sheet.

 

[excelbytes]

A typo of a sheet name was the issue. Thanks for the explanation and the formula. Looks perfect.