I want to sum over cells in column A based on a criterion on column B. The
criteria is kind of complicated, and a custom function [Fn(S as string) as
byte] has been written for the purpose. The formula

= IF(Fn(B2)0, A2,0)

works fine, so I tried the array formula

{=SUM(IF(Fn(B2:B5)0, A2:A5,0))}

but that doesn¡¦t work. (returning a #VALUE! error)

Suggestions are most welcome!

It is probably because your function cannot handle and return an array,
being single cell driven. Post the function and the criteria, we'll try and
help.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"robot" wrote in message
...
I want to sum over cells in column A based on a criterion on column B. The
criteria is kind of complicated, and a custom function [Fn(S as string) as
byte] has been written for the purpose. The formula

= IF(Fn(B2)0, A2,0)

works fine, so I tried the array formula

{=SUM(IF(Fn(B2:B5)0, A2:A5,0))}

but that doesn¡¦t work. (returning a #VALUE! error)

Suggestions are most welcome!

Hi Bob,

You are right in that the function is single-cell driven. I just thought
that in the case of array formulae, the value of each cell would be feed
consecutively into the function, but apparently that's wishful thinking on
my part.

The function Fn returns the number of "Leading stars" in a cell, as follows:

Function Fn(S As String) As Byte
Dim Count As Byte
Count = 0
While Left(S, 1) = "*"
S = Right(S, Len(S) - 1)
Count = Count + 1
Wend
nLeftStar = Count
End Function

With an array like the following:

12 *B
23 C
14 **D
33 *EE

I would like to add up all cells corresponding to a single leading *, ie
with Fn value = 1 (in the array above, the sum would be 12+33=45) using one
formula. How should I proceed?

Okay, this should do it for you

'---------------------------------------------------------------------
Function Fn(rng As Range) As Variant
'---------------------------------------------------------------------
Dim cell As Range, row As Range
Dim i As Long, j As Long
Dim aryCount As Variant

If rng.Areas.Count 1 Then
Fn = CVErr(xlErrValue)
Exit Function
End If

If rng.Cells.Count = 1 Then
aryCount = rng
aryCount = CountStar(rng.Value)
Else
aryCount = rng.Value
i = 0
For Each row In rng.Rows
i = i + 1
j = 0
For Each cell In row.Cells
j = j + 1
aryCount(i, j) = CountStar(cell.Value)
Next cell
Next row
End If

Fn = aryCount

End Function

Private Function CountStar(val)
CountStar = 0
While Left(val, 1) = "*"
val = Right(val, Len(val) - 1)
CountStar = CountStar + 1
Wend
End Function

You should be able to do it with just formulae, so I will try that and post
back.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"robot" wrote in message
...
Hi Bob,

You are right in that the function is single-cell driven. I just thought
that in the case of array formulae, the value of each cell would be feed
consecutively into the function, but apparently that's wishful thinking on
my part.

The function Fn returns the number of "Leading stars" in a cell, as
follows:

Function Fn(S As String) As Byte
Dim Count As Byte
Count = 0
While Left(S, 1) = "*"
S = Right(S, Len(S) - 1)
Count = Count + 1
Wend
nLeftStar = Count
End Function

With an array like the following:

12 *B
23 C
14 **D
33 *EE

I would like to add up all cells corresponding to a single leading *, ie
with Fn value = 1 (in the array above, the sum would be 12+33=45) using
one
formula. How should I proceed?

I *think* this will work for you, without the need for the custom function.

=SUMPRODUCT(--(LEN(SUBSTITUTE(B2:B5,"*","",1))=LEN(SUBSTITUTE(B2 :B5,"*",""))),--(LEN(SUBSTITUTE(B2:B5,"*","",1))=LEN(B2:B5)-1),A2:A5)

"robot" wrote:

Hi Bob,

You are right in that the function is single-cell driven. I just thought
that in the case of array formulae, the value of each cell would be feed
consecutively into the function, but apparently that's wishful thinking on
my part.

The function Fn returns the number of "Leading stars" in a cell, as follows:

Function Fn(S As String) As Byte
Dim Count As Byte
Count = 0
While Left(S, 1) = "*"
S = Right(S, Len(S) - 1)
Count = Count + 1
Wend
nLeftStar = Count
End Function

With an array like the following:

12 *B
23 C
14 **D
33 *EE

I would like to add up all cells corresponding to a single leading *, ie
with Fn value = 1 (in the array above, the sum would be 12+33=45) using one
formula. How should I proceed?

Perhaps a little bit more concise:

=SUMPRODUCT(--(LEFT(B2:B5,1)="*"),--(LEFT(B2:B5,2)<"**"),A2:A5)

"robot" wrote:

Hi Bob,

You are right in that the function is single-cell driven. I just thought
that in the case of array formulae, the value of each cell would be feed
consecutively into the function, but apparently that's wishful thinking on
my part.

The function Fn returns the number of "Leading stars" in a cell, as follows:

Function Fn(S As String) As Byte
Dim Count As Byte
Count = 0
While Left(S, 1) = "*"
S = Right(S, Len(S) - 1)
Count = Count + 1
Wend
nLeftStar = Count
End Function

With an array like the following:

12 *B
23 C
14 **D
33 *EE

I would like to add up all cells corresponding to a single leading *, ie
with Fn value = 1 (in the array above, the sum would be 12+33=45) using one
formula. How should I proceed?

Hi,

Both work for me, I certainly got more than what I bargain for! Thank you so
much, you guys are really great!