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?

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:

AA 1) Sheet2 2) Sheet3 3) Sheet4 4) Sheet5 5) Sheet6 6) Sheet7 7) Sheet8 8) Sheet9 9) Sheet10Now on Sheet1, cell A1, just enter the formula,

=SUMPRODUCT(COUNTIF(INDIRECT(""&AA1:AA9&"!B2"),"Yes"))

See how that works.

MIKE

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:=COUNTIF(B2,"YES")

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 TabThis should give you

=SUM('firstsheetname:lastsheetname'!in the formula bar8 - 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.

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 Next End SubOf 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.

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:

AA 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 10With a Space between Sheet and number you will need to use this formula:

=SUMPRODUCT(COUNTIF(INDIRECT( "'"& AA1:AA9 &"'!B2"),"Yes"))

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

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

should also work.

MIKE

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:

=CountYes()You must use the parenthesis, but the function takes no arguments.

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

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

thanks mmcconaghy & DerbyDad03 all ur solutions worked.

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

Ask Your Question

Weekly Poll

Do you think Monopoly should update its pieces?

Discuss in The Lounge

Poll History