Building on the indirect function..

I'd suggest putting a list of addresses in a range.

like
jack john mary
sales_jan!c6:f40
sales_feb!c6:f40
sales_mar!c6:f40

then your formula could look like
=sum(countif(indirect($a2:$a4);b$1))
enter as array function with ctrl shift enter

then copy the formula down
then copy the formulas right
(careful while copying these single cell array formulas, you cant
copy 'over' them as eg d1 to d1:d4

the address list CAN be build with CELL("address",cellref) functions


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Sukismomma wrote :

I want to do the same thing and have the results populated next to
the name on the summary sheet. What I am struggling with is how I
list my sheet names in the function code. They are extremely
different - they are names of projects - and there will be sheets
added as well as removed from the workbook when projects close. Is
there a way I can list the sheets to search for my specific resource
name with a more generic sheet search rather than by typing in each
individual sheet? I currently have 33 project tabs and 45 resources.

"keepITcool" wrote:

oops, that must be:

=SUMPRODUCT(
COUNTIF(INDIRECT("Sheet"&ROW(INDIRECT("1:50"))&"!C 6:F40");"jack"))

or =SUM( countif() ) entered as array function

--
keepITcool
www.XLsupport.com | keepITcool chello nl | amsterdam


keepITcool wrote :

Nope,
no VBA needed.
Try a worksheetfunction:

=COUNTIF(INDIRECT("Sheet"&ROW(INDIRECT("1:50"))&"! C6:F40");"jack")


--
keepITcool
www.XLsupport.com | keepITcool chello nl | amsterdam


hans wrote :

I want to know how often a name (say jack) occurs in a
spreadsheet. The name has to be found in the range c6:f40
but......... i have 50 sheets. named 1, 2, 3, 4, .......50
and i want to know the sum of how often a name occurs in all
sheets together.
I think this has to be done in vba!


Can someone help me?
Greetings Hans