Solved Countif across multiple worksheets

September 5, 2012 at 01:44:52
Specs: Windows Vista
hello I have 100 worksheets names 1 to 100 having same format having "YES" or "NO" in B2 of each worksheet I would like to count no of YES in B2 of each sheet in summary sheet.
I m using using Excel 2003 can I use this with Countif , and how?

See More: Countif across multiple worksheets

Report •

September 5, 2012 at 07:06:28
✔ Best Answer
You can't do it with just a =COUNTIF() function,
you will need a bit more.

Try this:

First put your Sheet Names in cells AA1 through AA100,
I've used only 9 here as an example, and you can use any column you like.

So your data looks like:

1) Sheet2
2) Sheet3
3) Sheet4
4) Sheet5
5) Sheet6
6) Sheet7
7) Sheet8
8) Sheet9
9) Sheet10

Now on Sheet1, cell A1, just enter the formula,


See how that works.


Report •

September 5, 2012 at 07:13:45
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.

Report •

September 5, 2012 at 07:19:04
If you prefer Mike's method over mine, this macro will make it a bit easier to put the Sheet names in column AA:

Sub List_SheetNames()
 For sht = 1 To Sheets.Count
   Range("AA" & sht) = Sheets(sht).Name
End Sub

Of course, if your sheets really are named 1,2,...100 then a simple Auto-fill will work.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

Related Solutions

September 5, 2012 at 08:46:34
Here is an updated version of the formula.
The original will fail if you have a space in the Sheet Names.

So if your data looked like this:

1) Sheet 2
2) Sheet 3
3) Sheet 4
4) Sheet 5
5) Sheet 6
6) Sheet 7
7) Sheet 8
8) Sheet 9
9) Sheet 10

With a Space between Sheet and number you will need to use this formula:


Don't know if you can see them,
but I've added a single tick mark between the first set of quotes
and a single tick mark just before the exclamation mark.

Also, if you wish you can use a Define Name as your range.

If you define the range of cells from AA1 thru AA9 as MyList then

=SUMPRODUCT(COUNTIF(INDIRECT( "'"& Mylist &"'!B2"),"Yes"))

should also work.


Report •

September 5, 2012 at 10:53:10
If a UDF is acceptable, place the code below in a Standard Module and enter this function in any cell - except for B2 - in any sheet:


You must use the parenthesis, but the function takes no arguments.

Function CountYes()
  For sht = 1 To Sheets.Count
   If Sheets(sht).Range("B2") = "YES" Then
     tmpYes = tmpYes + 1
   End If
 CountYes = tmpYes
End Function

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

October 7, 2012 at 23:21:53
thanks mmcconaghy & DerbyDad03

all ur solutions worked.


Report •

Ask Question