Count value in a range across workbook

  • Thread starter Thread starter Mike
  • Start date Start date
M

Mike

Using Excel 2003. Novice user with little VBA experience/knowledge. I am
trying to have my workbook count each time a value is displayed in range
A9:A18 on each sheet. A new sheet will be added each week and I do not want
to have to update the formula for every sheet that one is added. Column A
contains account numbers. Column C I would like to have the total amount of
times the account number in A is found throughout the entire workbook. C2
displays the count result of account number is A2, C3 displays A3
counts...etc. There are approx 2000 account numbers in column A and some are
added periodically. This sheet is called Call Counts. I cannot have the
fomula for the account number counting including the account number displayed
in this sheet, just all others existing and added at a later date.

Any help would be greatly appreciated.
 
Modified from code found at
http://www.mrexcel.com/forum/showthread.php?t=36082

The function below will search all sheets within range to find the value
entered as a parameter.
Examples:

To count the number of time the value A appears in the entire columnA of
every worksheet:

=CountAllSheetIf(A:A,"A")

To count the number of tiem the value in cell B1 occurs in range A1:A3 in
every workbook
=CountAllSheetIf(A1:A3, B1)

To skip a particular sheet, uncomment the If and End if lines


Option Explicit

Public Function CountAllSheetIf(rng As Range, ToFind As Variant)
Application.Volatile

Dim shList()
Dim shCount, i

shCount = Sheets.Count

CountAllSheetIf = 0

For i = 1 To shCount
'If Sheets(i).Name <> "Sheet1" Then 'change Sheet1
CountAllSheetIf = _
WorksheetFunction.CountIf(Sheets(i).Range(rng.Address),
ToFind) + _
CountAllSheetIf
'End If
Next i

End Function
 
Thank you for your help. I tried the countallsheetif function and it
returned #NAME?. I looked at the link you referenced. In there is said to
download an add-in called morefunc.xll. Everywhere I looked for this it sent
me to the same page to download at
http://xcell05.free.fr/morefunc/english/#Download but it doesn't work. I
don't know if I have done something wrong or not.
 
Morefunc.dll will not do what you wish. Read further down the message by
Jaafar Tribak.

Possibly, you misspelled CountAllSheetIf in your cell but more likely, it is
me not telling you to copy the function into a standard module in your
workbook:

Press Alt/F11, Insert, Module and paste the code from and including "Option
Explicit" to "End Function" and without all the > symbols.

If any of he lines show red, this indicates a wrapping problem. Proceed to
the end of the first line that is red and press delete until the line of
code below moves to the line of the cursor; move cursor to the end of the
line and press enter.

After the function is in the module, it should work.
 
Back
Top