COUNTIF non-contiguous range and not null criteria

=COUNTIF(A1:A5,"John Jones")

Or

=COUNTIF(A1:A5, B1)

Where B1 houses the text you want to count.

To do multiple range just use multiple countifs:

=COUNTIF(A1:A5,"John Jones") + COUNTIF(A8:A12,"John Jones") + COUNTIF(A15:A19,"John Jones")

Or just count the whole column

=COUNTIF(A:A,"John Jones")

Thanks for the suggestions.

Unfortunately I don't know in advance what it is that I'm looking up, to use the name as the criteria - only that it's going to be a name.

So you're not aware of a method using COUNTIF to specify a non-null criteria..?

And I take it from the "use multiple COUNTIF" suggestion that it's also not possible to specify different ranges..? (I thought it might have been possible using some clever formula like INDIRECT to gather the ranges within COUNTIF, but I've not been successful in doing so.)

See,

http://www.mrexcel.com/board2/viewtopic.php?t=29977&postdays=0&postorder=asc&start=10

A-ha! Thanks for the pointer, Brian.
=SUM(COUNTIF(INDIRECT({"A1:A5","A8:A12","A15:A19","A22:A26","A29:A33"}),""))

...Now to work out how to specify non-null as the criteria. I can't even get it to work entering a value of ="" in, say, B1 and then do the formula as:
=SUM(COUNTIF(INDIRECT({"A1:A5","A8:A12","A15:A19","A22:A26","A29:A33"}),"<>"&B1))

One way...

=SUMPRODUCT(--(LEN(ArrayUnion(A1:A5,A8:A12,A15:A19,A22:A26,A29:A33))>0))

Here is the code for ArrayUnion:

Option Explicit

Function ArrayUnion(ParamArray Arg() As Variant) As Variant
' Code: Juan Pablo González
' Spec: Aladin Akyurek
' May 4, 2003
' Ref: http://makeashorterlink.com/?P20022174
' Mod: Nov 3, 2003, to reduce number of ReDim Preserve calls.
Dim TempUnion() As Variant
Dim i As Long, Itm As Variant, Ctr As Long
ReDim TempUnion(1 To UBound(Arg) - LBound(Arg) + 1) As Variant
For i = LBound(Arg) To UBound(Arg)
Arg(i) = Arg(i)
If IsArray(Arg(i)) Then
For Each Itm In Arg(i)
Ctr = Ctr + 1
If Ctr > UBound(TempUnion) Then
ReDim Preserve TempUnion(1 To UBound(TempUnion) * 2) As Variant
End If
'ReDim Preserve TempUnion(1 To Ctr) As Variant
TempUnion(Ctr) = Itm
Next Itm
Else
Ctr = Ctr + 1
If Ctr > UBound(TempUnion) Then
ReDim Preserve TempUnion(1 To UBound(TempUnion) * 2) As Variant
End If
'ReDim Preserve TempUnion(1 To Ctr) As Variant
TempUnion(Ctr) = Arg(i)
End If
Next i
If Ctr < UBound(TempUnion) Then
ReDim Preserve TempUnion(1 To Ctr) As Variant
End If
ArrayUnion = TempUnion
End Function

To add this to your workbook:

Activate Tools|Macro|Visual Basic Editor;
Activate Insert|Module;
Copy the UDF above and paste it in the pane entitled "...(code)".
Activate File|Close and Return to Microsoft Excel.

I can see that formula coming in handy. Thanks Aladin!

I'm just surprised that I can't find a way to do this...

Currently I'm cheating by using a criteria of ">=A" to get the result I need...

Hi,

As we are dealing with text here, why not:

=SUMPRODUCT(--(T(CHOOSE({1,2,3,4},A1:A5,A8:A12,A15:A19,A22:A26,A29:A33))<>""))