Custom function - Countif 5

[waynerenaud]

I am trying to do a countif that will look for an entry in column D on multiple sheets and return the count value.
The formula:
=COUNTIF(Sheet1:Sheet56!D,A1)
but i get a #VALUE! error
if I just look at one sheet ie:
=COUNTIF(Sheet1!D,A1)
it works fine.
Anyone know how to get it to look at all the sheets ?

or alternately

can anyone guide me on how to write a custom function that will do a countif and loop through all the worksheets in a workbook.
Please Help

 

[xlbo]

Unfortunately (for you), countif is not one of the functions that can use 3D references (ie across multiple sheets) so here is a UDF

Function 3DCountif(lookWhere as range, col as string)
Lookfor = lookWhere.text
TempCnt= 0
for each wks in thisworkbook.worksheets
lRow = wks.range(col & "65536&quot.end(xlup).row
for each c in wks.range(cells(1,col),cells(lRow,col))
if c.text = Lookfor then
tempCnt = TempCnt + 1
else
end if
next
next
3DCountif = TempCnt
end function

Rgds
Geoff
Si hoc legere scis, nimis eruditionis habes

 

[shaggi]

Spot on XLBO, star for that one,
waynerenauld could also just add the counts though couldn't he?(although I much prefer your UDF)

COUNTIF(Sheet1!D,A1)+COUNTIF(Sheet2!D,A1)+COUNTIF(Sheet3!D,A1)etc...

 

[xlbo]

Cheers Shaggi - s'pose they could add them up but not a very pretty formula.....for 56 sheets - also may run into character restrictions depending on the length of name of the sheets

Rgds
Geoff
Si hoc legere scis, nimis eruditionis habes

 

[waynerenaud]

Many Thanks xlbo. I have had this post on another forum for a week and a half with 0 responses. This is the first time I have used this forum and got a great reply in less than a day. A star is on its way
Wayne

 

[waynerenaud]

ok - please be patient here.........
I have pasted this code into a module. The lines Function 3DCountif(lookWhere as range, col as string)
and
3DCountif = TempCnt
turned red (what does red text indicate as an error)and it didnt show in my functions list. I figured it could be because of the 3 so I changed 3DCountif to sheetcountif in both places. Now it shows in black and it is in my functions list. I tried the following formulas (+other variations)
=sheetcountif(A1,D) gets #value
=sheetcountif($A$1,D) gets #value
=sheetcountif(A1,D) gets #value
=sheetcountif(Sheet1!A1,D)gets #value
plus any other variation I could think of
This is probably my lack of understanding of the code. Could you help explain the code and what I should put in the formula to count entries matching Sheet1!A1 in column D on all 56 sheets. Thanks

 

[Zathras]

I'm afraid Geoff threw you a curve. The parameters for his function are quite a bit different from the parameters used by COUNTIF. To begin with, they are reversed. Secondly, the col parameter is a string which means that you would have to enter it as "D" (with the quotes). Unfortunately even after making that leap, it still wouldn't work because he tries to use it as a number in a Cells property expression. Still more misfortune awaits, because if that bug is fixed, the function is still unsatisfactory: a change in the column would not be recognized a change that requires recalculation of the function, and thus would continue to report a previous total. (More on this last point below.)

One other thing: A function name cannot start with a number. That is why 3DCountif is showing in red (in 2 places).

Here is a form of the function that is a bit nearer to what you want:
[blue]

Option Explicit

Function ThreeDCountIf(ARange As Range, Criteria As String)
Dim TempCnt As Long
Dim RangeAddress As String
Dim wks As Worksheet
Dim c As Range
  TempCnt = 0
  RangeAddress = ARange.Address
  For Each wks In ThisWorkbook.Worksheets
    With wks
      For Each c In .Range(RangeAddress)
          If c.Text = Criteria Then
             TempCnt = TempCnt + 1
          End If
      Next c
    End With
  Next wks
  ThreeDCountIf = TempCnt
End Function

[/color]
To be sure, it is not a powerful as COUNTIF since only an equals condition on one cell is tested for.
[blue]

   =ThreeDCountIf(D1:D7,A1)

[/color]
This function is not foolproof, however. Continuing from above.... If a change is made on one of the other sheets, Excel does not recalculate the function on the sheet where this function is entered. (A change on the same sheet is handled properly). So... If the situation is such that changes on the other sheets are common, then you had better abandon the idea of using a UDF completely, and go for a different interface. Perhaps a button that can be clicked when the user wants to see the count. Or process the Worksheet_Activate event for the sheet that contains the cell where you want to see the count and execute the macro to do the count whenever that event fires.

 

[xlbo]

D'OH, D'OH and D'OH - I MUST stop writing stuff straight into TT and check it at least compiles in xl. Thank you very much Zathras for pointing out the errors - star to you

Rgds
Geoff
Si hoc legere scis, nimis eruditionis habes

 

[waynerenaud]

Many Thanks Zathras, your code worked great and it has helped me learn a bit more.
It needed to be a formula so that I could fill it down. I added the following to a module which I run from a button on the sheet which seems to work ok. Please feel free to correct/improve this code.

Sub Recalc()
Worksheets("Sheet1&quot.Range("B1&quot.Activate
Worksheets("Sheet1&quot.Range("B1&quot.Select
Do While Selection <> &quot;&quot;
ActiveCell.FormulaR1C1 = &quot;=ThreeDCountIf(R1C[2]:R200C[2],Sheet1!RC[-1])&quot;
ActiveCell.Offset(1, 0).Select
Loop
End Sub

Thanks again everyone for the help.