CountIf from multiple sheets in another workbook

[JoshBrunz]

i have 1 workbook with about 20 sheets, i need to search this workboo
for a name in one column on each sheet. i made a list of the sheets i
the 2nd workbook in column a. i tried this formula in column b, but
can't get it to recognize the cell in my formula:

=SUM(SUMIF(INDIRECT("'[cfshows.xls]&A2'!U:U"),"Brown, Bob")

 

[lhkittle]

i have 1 workbook with about 20 sheets, i need to search this workbook

for a name in one column on each sheet. i made a list of the sheets in

the 2nd workbook in column a. i tried this formula in column b, but i

can't get it to recognize the cell in my formula:



=SUM(SUMIF(INDIRECT("'[cfshows.xls]&A2'!U:U"),"Brown, Bob"))

Hi Josh,

What do you want the formula to return once it finds the name in one of the sheets?

Regards,
Howard

 

[lhkittle]

What do you want the formula to return once it finds the name in one of the sheets?



Regards,

Howard

My bad, looks like you want a count of the name from all the columns.

Howard

 

[JoshBrunz]

i have 1 workbook with about 20 sheets, i need to search thi workbook

for a name in one column on each sheet. i made a list of the sheet in

the 2nd workbook in column a. i tried this formula in column b, but i

can't get it to recognize the cell in my formula:



=SUM(SUMIF(INDIRECT("'[cfshows.xls]&A2'!U:U"),"Brown, Bob"))

Hi Josh,

What do you want the formula to return once it finds the name in one o
the sheets?

Regards,
Howard

just a count of how many times the name bob brown appears in the
column on each pag

 

[lhkittle]

just a count of how many times the name bob brown appears in the U

column on each page

JoshBrunz

Well, one way, on each sheet in a discreet cell enter this formula, (I used F1 on each sheet). Adjust formula to that sheet's name.

=COUNTIF(Sheet2!U:U,Sheet1!A2)

Bob Brown is in cell A2 of sheet1. And A2 could be a drop down with other names.

Then on sheet1 this formula =SUM(Sheet2:Sheet6!F1)

Where sheet2 is the first sheet and sheet6 is the last sheet.

Regards,
Howard