|Excel 2003 does not support COUNTIF across multiple sheets.|
While there are User Defined Functions (UDF) written in VBA that will do this for you, a fairly simple workaround is this:
1 - Select the left most sheet tab of your group of 100.
2 - Hold the Shift key and select the right most Tab. This will group all of the sheets together.
3 - Select any cell in the left most sheet that you know to be empty on all sheets. I'll use A1 in this example.
4 - With the sheets grouped together, enter this formula in A1:
This will put that formula in A1 of every sheet, where it will return either 0 or 1 based on the contents of B2.
Be careful not to make any other changes while the sheets are grouped because that change will be made in every sheet.
5 - Select your Summary sheet, which should de-select the grouped sheets.
6 - In your Summary sheet, select the cell where you want the total count.
7 - Enter =SUM( then, without hittng Enter...
- 7a -Select the left most sheet tab of your group of 100
- 7b - Hold the Shift key and select to Right most Tab
This should give you =SUM('firstsheetname:lastsheetname'! in the formula bar
8 - Type A1) after the exclamation point and hit Enter.
This should sum all of the COUNTIF formulas on your sheets and return the total number of YES entries.
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.