As I said, use SUMPRODUCT (but don't use whole columns as I put in the last
post, SP needs a defined range)

=SUMPRODUCT(--('I:\CCR\SAM\7057\[NSIR2007_7057.xls]NSIR
Data'!$E2:$E200="Teamname",--('I:\CCR\SAM\7057\[NSIR2007_7057.xls]NSIR
Data'!$H2:$H200="Y")

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"JDB" wrote in message
...
I've figured out how to do the below, simp,y adding a + and then adding
the
next sheet and so on. However, I've come up against another obstacle, I
need
to then create a formula to check if column E is 'Teamname' and then check
column H for a Y or N and return the count on the results sheet. Possible?

"JDB" wrote:

Hi,

I currently have 5 different workbooks for use by 5 seperate team
members. I
need to collate the data from those 5 workbooks into a seperate book.

An example workbook is NSIR2007_7057.

Each workbook is laid out identically - all the columns have the same
headings. For example, I need to count the number of times a team name
appears, can I extend the formula below to include the other 4 workbooks
and
if so, how do I do it?

=COUNTIF('I:\CCR\SAM\7057\[NSIR2007_7057.xls]NSIR Data'!$E:$E,A2)

(E is the column containing the team name and A2 is the cell on the
results
workbook specifying the team name to look for)

Any help is greatly appreciated as always!