I have 12 worksheets starting from Jan. to Dec.. There are staff names
in the A column in each worksheet and most of them are in the same row
on each worksheet. I have created names in the left column according to
the staff names and set up an formula in a defined column e.g. AG

10/01 10/02.......... 10/31
Davis Chan v v v =Sum((Davis_Chan="V")*1)
Ada Au v =Sum((Ada_Au="V")*1)

But I couldn't figure out a formula to count the string "V" for
all "Davis Chan" in all worksheets since "Davis Chan" can be in
different rows in all worksheets. Does anybody have any idea if I want
to put the new formula in a separate worksheet ?

Louis

Hi!

Try this:

Create a list of your sheet names:

Jan
Feb
Mar
...
Dec

Give this list a defined name, say, Snames

On your summary sheet starting in cell A1 you have the staff names listed:

A1 = Davis Chan
A2 = Ada Au

In B1 enter this formula and copy down as needed:

=SUMPRODUCT(SUMIF(INDIRECT("'"&Snames&"'!A1:A15"), A1,INDIRECT("'"&Snames&"'!AG1:AG15")))

Adjust the ranges to suit.

=Sum((Davis_Chan="V")*1)

Use this instead:

=COUNTIF(Davis_Chan,"V")

Biff

wrote in message
ups.com...
I have 12 worksheets starting from Jan. to Dec.. There are staff names
in the A column in each worksheet and most of them are in the same row
on each worksheet. I have created names in the left column according to
the staff names and set up an formula in a defined column e.g. AG

10/01 10/02.......... 10/31
Davis Chan v v v =Sum((Davis_Chan="V")*1)
Ada Au v =Sum((Ada_Au="V")*1)

But I couldn't figure out a formula to count the string "V" for
all "Davis Chan" in all worksheets since "Davis Chan" can be in
different rows in all worksheets. Does anybody have any idea if I want
to put the new formula in a separate worksheet ?

Louis

Biff,

Thanks for reply. One thing I don't know - how to create a list of
sheet names and define a name for them. Should I type Jan to Dec. on a
summary worksheet in a column and then create a name for them e.g.
summary!$A$1:$A$12. In addition, if I want to separate the count in
different months, can I just change the Snames to include the concerned
month e.g. Oct instead of Jan.to Dec. ?

Louis