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!

If you are just doing a count in that way, is there any reason why you can't
just use
=COUNTIF('I:\CCR\SAM\7057\[NSIR2007_7057.xls]NSIR Data'!$E:$E,A2)
+COUNTIF('I:\CCR\FRED\7058\[NSIR2007_7058.xls]NSIR Data'!$E:$E,A2)
+COUNTIF('I:\CCR\BILL\7059\[NSIR2007_7059.xls]NSIR Data'!$E:$E,A2)
+COUNTIF('I:\CCR\JIM\7060\[NSIR2007_7060.xls]NSIR Data'!$E:$E,A2)
+COUNTIF('I:\CCR\JOE\7061\[NSIR2007_7061.xls]NSIR Data'!$E:$E,A2)
(changing the paths & filenames to suit) ?
--
David Biddulph

"JDB" wrote in message
...
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!

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!

COUNTIF doesn't work with a closed workbook, so I suggest that you change it
to SUMPRODUCT. Then just string the formulae

=SUMPRODUCT(--('I:\CCR\SAM\7057\[NSIR2007_7057.xls]NSIR Data'!$E:$E=A2))+
SUMPRODUCT(--('I:\CCR\SAM\7057\[NSIR2007_8081.xls]NSIR Data'!$E:$E=A2))



--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"JDB" wrote in message
...
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!

Thanks for that. The SUMPRODUCT worked much better than stringing COUNTIF.

Now I would like to be able to take the team name from column E and look in
Column H for a Y or N and then report back to the results sheet the count of
Y's & N's (in seperate columns). So if CCR A had three Y's and two N's the
results sheet shows the total team count (where I used the SUMPRODUCT) in
column B, Correct Results (represented by a Y on the individual sheets) in
column C and Errors (represented by a N on the individual sheets) in column D.

Is this possible?

"Bob Phillips" wrote:

COUNTIF doesn't work with a closed workbook, so I suggest that you change it
to SUMPRODUCT. Then just string the formulae

=SUMPRODUCT(--('I:\CCR\SAM\7057\[NSIR2007_7057.xls]NSIR Data'!$E:$E=A2))+
SUMPRODUCT(--('I:\CCR\SAM\7057\[NSIR2007_8081.xls]NSIR Data'!$E:$E=A2))



--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)

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!

I was still saying you had to string SPs, just that Sp would work whereas
COUNTIF won't on a closed workbook.

Already answered other part in earlier response.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"JDB" wrote in message
...
Thanks for that. The SUMPRODUCT worked much better than stringing COUNTIF.

Now I would like to be able to take the team name from column E and look
in
Column H for a Y or N and then report back to the results sheet the count
of
Y's & N's (in seperate columns). So if CCR A had three Y's and two N's the
results sheet shows the total team count (where I used the SUMPRODUCT) in
column B, Correct Results (represented by a Y on the individual sheets) in
column C and Errors (represented by a N on the individual sheets) in
column D.

Is this possible?

"Bob Phillips" wrote:

COUNTIF doesn't work with a closed workbook, so I suggest that you change
it
to SUMPRODUCT. Then just string the formulae

=SUMPRODUCT(--('I:\CCR\SAM\7057\[NSIR2007_7057.xls]NSIR Data'!$E:$E=A2))+
SUMPRODUCT(--('I:\CCR\SAM\7057\[NSIR2007_8081.xls]NSIR Data'!$E:$E=A2))



--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)

Bob,

Thanks for your help so far - I didn't even know SUMPRODUCT existed, so that
was genius!

The formula from your other post (below) returns a missing parenthesis error
and I can't figure where to put it? I've copied below as I've entered it - am
I missing something?

=SUMPRODUCT(--('I:\CCR\SAM\7057\[NSIR2007_7057.xls]NSIR
Data'!$E3:$E9999="CCR A",--('I:\CCR\SAM\7057\[NSIR2007_7057.xls]NSIR
Data'!$H3:$H9999="Y")

Your continued assistance is very much appreciated and my team are aware
that I didn't get there by myself!!!

"Bob Phillips" wrote:

I was still saying you had to string SPs, just that Sp would work whereas
COUNTIF won't on a closed workbook.

Already answered other part in earlier response.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"JDB" wrote in message
...
Thanks for that. The SUMPRODUCT worked much better than stringing COUNTIF.

Now I would like to be able to take the team name from column E and look
in
Column H for a Y or N and then report back to the results sheet the count
of
Y's & N's (in seperate columns). So if CCR A had three Y's and two N's the
results sheet shows the total team count (where I used the SUMPRODUCT) in
column B, Correct Results (represented by a Y on the individual sheets) in
column C and Errors (represented by a N on the individual sheets) in
column D.

Is this possible?

"Bob Phillips" wrote:

COUNTIF doesn't work with a closed workbook, so I suggest that you change
it
to SUMPRODUCT. Then just string the formulae

=SUMPRODUCT(--('I:\CCR\SAM\7057\[NSIR2007_7057.xls]NSIR Data'!$E:$E=A2))+
SUMPRODUCT(--('I:\CCR\SAM\7057\[NSIR2007_8081.xls]NSIR Data'!$E:$E=A2))



--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)

I missed a couple of parentheses. Try this instead

=SUMPRODUCT(--('I:\CCR\SAM\7057\[NSIR2007_7057.xls]NSIR
Data'!$E3:$E9999="CCR A"),
--('I:\CCR\SAM\7057\[NSIR2007_7057.xls]NSIR Data'!$H3:$H9999="Y"))

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"JDB" wrote in message
...
Bob,

Thanks for your help so far - I didn't even know SUMPRODUCT existed, so
that
was genius!

The formula from your other post (below) returns a missing parenthesis
error
and I can't figure where to put it? I've copied below as I've entered it -
am
I missing something?

=SUMPRODUCT(--('I:\CCR\SAM\7057\[NSIR2007_7057.xls]NSIR
Data'!$E3:$E9999="CCR A",--('I:\CCR\SAM\7057\[NSIR2007_7057.xls]NSIR
Data'!$H3:$H9999="Y")

Your continued assistance is very much appreciated and my team are aware
that I didn't get there by myself!!!

"Bob Phillips" wrote:

I was still saying you had to string SPs, just that Sp would work whereas
COUNTIF won't on a closed workbook.

Already answered other part in earlier response.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"JDB" wrote in message
...
Thanks for that. The SUMPRODUCT worked much better than stringing
COUNTIF.

Now I would like to be able to take the team name from column E and
look
in
Column H for a Y or N and then report back to the results sheet the
count
of
Y's & N's (in seperate columns). So if CCR A had three Y's and two N's
the
results sheet shows the total team count (where I used the SUMPRODUCT)
in
column B, Correct Results (represented by a Y on the individual sheets)
in
column C and Errors (represented by a N on the individual sheets) in
column D.

Is this possible?

"Bob Phillips" wrote:

COUNTIF doesn't work with a closed workbook, so I suggest that you
change
it
to SUMPRODUCT. Then just string the formulae

=SUMPRODUCT(--('I:\CCR\SAM\7057\[NSIR2007_7057.xls]NSIR
Data'!$E:$E=A2))+
SUMPRODUCT(--('I:\CCR\SAM\7057\[NSIR2007_8081.xls]NSIR
Data'!$E:$E=A2))



--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)