COUNTIF non-contiguous range and not null criteria

[jasonconlon]

Greetings!

To set the scene -- I've got a column of formulas that are doing some test, and returning either a null ("") value or a name (e.g. John Brown). Every so often there's a break in the column to do a subtotal, then some blank rows, then it returns to the same formula of null-or-name. This is in a set-pattern of 5 formulas (for each weekday), subtotal, blank row; repeat... - for a set-total of 5 iterations.

For example:
[null]
John Jones
John Jones
John Jones
[null]
Total

John Jones
John Jones
[null]
John Jones
John Jones
Total

etc...

I'm looking to do a COUNTIF for each instance of a name (i.e. non-null), specifying a non-adjacent range of cells.

~Question 1~
I can work out how to set the criteria to test for null values:
=COUNTIF(A1:A5,"")
but how do you express a non-null criteria?

~Question 2~
Is it possible, using COUNTIF, to specify several ranges?
e.g. something equivalent to:
=COUNTIF({A1:A5,A8:A12,A15:A19,A22:A26,A29:A33},"")

 

[DRJ]

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

 

[jasonconlon]

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.)

 

[Brian from Maui]

See,

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

 

[jasonconlon]

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

 

[Aladin Akyurek]

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.

 

[jasonconlon]

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...

 

[fairwinds]

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