Values of non-contiguous cells

[TJ]

Hi

I am looking for a function that that can be used to replace the COUNTIF
worksheet function, but works on non-contiguous cells. Can someone point me
in the right direction.

Thanks
Tony

 

[Tom Ogilvy]

Maybe something like this:

Public Function MyCountif(ParamArray v())
Dim tot As Long, c As Variant
Dim rng As Range
tot = 0
c = v(UBound(v))
For i = LBound(v) To UBound(v) - 1
Set rng = Nothing
On Error Resume Next
Set rng = v(i)
On Error GoTo 0
If Not rng Is Nothing Then
tot = tot + Application.CountIf(v(i), c)
End If
Next
MyCountif = tot
End Function

Make sure you put it in a general module

sample usage:
=mycountif(A2:A5,C2:C5,E2:E5,H2:H5,">"&I2)