COUNTIF Across Multiple Worksheets

excelbytes

Board Regular
Joined
Dec 11, 2014
Messages
91
Office Version
  1. 365
Platform
  1. Windows
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.
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,414
Office Version
  1. 365
Platform
  1. Windows
Try
Excel Formula:
=SUMPRODUCT(COUNTIF(INDIRECT("'"&Sheets&"'!RC",0),"P"))
 
Solution

excelbytes

Board Regular
Joined
Dec 11, 2014
Messages
91
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Dec 30, 2008
Messages
12,414
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Dec 11, 2014
Messages
91
Office Version
  1. 365
Platform
  1. Windows
A typo of a sheet name was the issue. Thanks for the explanation and the formula. Looks perfect.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,123
Messages
5,622,870
Members
415,935
Latest member
kes1973

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top