count how often a name exists

[hans]

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

 

[Norman Jones]

Hi Hans,

Adapting the find example in Help, try:

Sub CountAll()
Dim sh As Worksheet
Dim iCtr As Long
Dim c As Variant
Dim firstaddress As String
Const MyStr As String = "Jack"

For Each sh In ActiveWorkbook.Worksheets
With sh.Range("C6:F40")
Set c = .Find(MyStr, LookIn:=xlValues)
If Not c Is Nothing Then
firstaddress = c.Address
Do
iCtr = iCtr + 1
Set c = .FindNext(c)
Loop While Not c Is Nothing And _
c.Address <> firstaddress
End If
End With
Next sh
MsgBox iCtr
End Sub

I think this has to be done in vba!

You may be interested in Jan Karel Pieterse's FlexFind Addin, available for
free download at:

http://www.jkp-ads.com/Download.htm

 

[Tom Ogilvy]

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*")

 

[keepITcool]

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 :

 

[keepITcool]

oops, that must be:

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

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

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


keepITcool wrote :

 

[Sukismomma]

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]

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 :