Is there a way to count/sum a Named Range which is not contiguous?

[pvr928]

Hi

I have a Named Range, being:

TEST_Range =TEST!$D$3:$D$9,TEST!$D$14:$D$22

Although I can treat this as a contiguous range within VBA, I am yet to find a worksheet way to do the same. For example:

=ROWS(Test_Range)

returns a '#REF!' error.

Sure, I can split up the range into a series of contiguous ranges and then aggregate them, but this would then defeat the purpose of having a single, non-contiguous, range.

Any help greatly appreciated.

Cheers

pvr928

 

[Aladin Akyurek]

Not all functions admit non-contiguous ranges, so ROWS() is one of them. Aggregation functions like SUM(), AVERAGE(), MAX() and so on will succeed.

 

[pvr928]

Thanks Aladin - looks like VBA it is!

Cheers

pvr928

 

[Aladin Akyurek]

Thanks Aladin - looks like VBA it is!

Cheers

pvr928

I suppose it depends on the calculations you require.

 

[pvr928]

Yes - perhaps someone smarter than myself can do something with a worksheet function.

It is pretty simple - the user is required to ensure all cells in the non-contiguous range are populated with text (this is done via a worksheet change event opening a UserForm whenever the non-contiguous range is selected). All I want is something to check whether all the cells are populated, something like:

If ( Count of Rows of Non-Contiguous Range = Count of Cells of Non-Contiguous Range Containing Text ) Then "Ok" Else "Check" )

Cheers

pvr928

 

[Aladin Akyurek]

Add the following function to your workbook if you will...

Function ArrayUnion(ParamArray Arg() As Variant) As Variant
' Code: Juan Pablo González
' Spec: Aladin Akyurek
' May 4, 2003
' Ref: TinyURL.com - shorten that long URL into a tiny URL
Dim TempUnion() As Variant
Dim i As Long, Itm As Variant, Ctr As Long
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
ReDim Preserve TempUnion(1 To Ctr) As Variant
TempUnion(Ctr) = Itm
Next Itm
Else
Ctr = Ctr + 1
ReDim Preserve TempUnion(1 To Ctr) As Variant
TempUnion(Ctr) = Arg(i)
End If
Next i
ArrayUnion = TempUnion
End Function

Redefine TEST_Range as:

=ARRAYUNION(Test!$D$3:$D$9,Test!$D$14:$D$22)

This...

If ( Count of Rows of Non-Contiguous Range = Count of Cells of Non-Contiguous Range Containing Text ) Then "Ok" Else "Check" )

becomes:

=IF(SUMPRODUCT(ISTEXT(Test_Range)+0)=COLUMNS(Test_Range),"ok","check")