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

Dim CntJack()
Dim sh as Worksheet, cnt as long
for each sh in activeworkbook.worksheets
cnt = cnt + application.countif(sh.range("C6:F40"),"Jack")
Next
msgbox "Jack cnt is " & cnt
End Sub

If the Jack could be part of a larger string use

cnt = cnt + application.countif(sh.range("C6:F40"),"*Jack*")

or for Jack at the beginning of a string

cnt = cnt + application.countif(sh.range("C6:F40"),"Jack*")

--
Regards,
Tom Ogilvy


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

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

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

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

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