Array Formulas with multiple arguments

Posted by Julie Braford on September 02, 2001 5:14 PM

Is there a limit to the number of arguments that can be used in an array formula? I am not able to get a sum,if & and statment as an array to work. It will accept it with an additional (uneccesary) set of parentheses, but it does not function properly. Here is the exact array formula:

{=sum(if((and(ColumnB&LT;(b$22+b$23),ColumnB>(B$22-B$23))),1,0))}

I am trying to count the number of results within a range that fall within a range (can't use contstant values), is there another way to do?

Thank you for your help.

Posted by Malcolm on September 02, 2001 5:36 PM

=SUM(IF((B1:B1000&LT;(B$22+B$23))*(B1:B1000>(B$22-B$23)),1,0))

Notes :
The AND function cannot be used in array formulas. An asterisk is used instead.
Complete column refs cannot be used.

Posted by Aladin Akyurek on September 02, 2001 10:56 PM

=SUMPRODUCT((B1:B1000&LT;B22+B23)*(B1:B1000>B22-B23))

which is entered normally.

Yep. Neither in SUMPRODUCT. However, there is a way to do just that:

=SUMPRODUCT((Used(B:B)&LT;B22+B23)*(Used(B:B)>B22-B23))

Used is a user-defined function (due to Harlan Grove). It requires that the latter formula must be in the same worksheet as the data of interest.

Aladin

Posted by Aladin Akyurek on September 02, 2001 11:00 PM

:
=SUMPRODUCT((B1:B1000 &LT; B22+B23)*(B1:B1000 > B22-B23))

Posted by Anon on September 02, 2001 11:29 PM

Can you post the UDF code please

Posted by Aladin Akyurek on September 03, 2001 1:32 AM

Re: Can you post the UDF code please

Anon,

Here it is.

Function Used(r As Range) As Range
'
' Harlan Grove
'
Dim q As Range
Set q = r.Parent.UsedRange.Cells(r.Parent.UsedRange.Cells.Count)
Set Used = Intersect(r, r.Parent.Range(Cells(1, 1), q))
End Function

To be complete:
In order to use this UDF, activate the option Tools|Macro|Visual Basic Editor. Activate Insert|Module. Paste the UDF in the open space on the window which carries the "(code)" bit in the title. Activate File|Close and Return to Microsoft Excel.

Aladin

==========

Posted by Anon on September 03, 2001 2:52 AM

Thanks. And of course, the code is so simple when you see it !

Harlan Grove