Using UDF in SUMPRODUCT

[JzP]

Hi,
I have a worksheet with an array of data similar to (but much bigger
than) the example below

Type Size Value
1 10ct 1
2 450gr p 20ct 2
1 3000GR P 100ct 3
2 200GR P 10ct 4
1 10GR P 15ct 5

I have named these cells "rngData"
Column A is a type column B is a product description and column C is a
value. In reality there are 8 description columns and 100+ value
columns.
I have another sheet which looks up this data sheet and does a
SUMPRODUCT where the attributes match what descriptions I specify.
So if on sheet 2 I have the following on rows 18 and 19:
ColumnA ColumnB ColumnC
1 10 =SUMPRODUCT(IF(INDEX(rngData,,
1)=A18,1,0)*(INDEX(rngData,,3)))
2 10 =SUMPRODUCT(IF(INDEX(rngData,,
1)=A18,1,0)*(INDEX(rngData,,3)))

The answers in cells C18 and C19 are 9 and 6 - correct.

What I want to do in addition is to somehow include rows where the
"CT" value (ie what is immediately before CT in column B) is within a
range.
I have written a function to find that value but if I try to include
the function in my SUMPRODUCT all I get is a #Value error.
What I am trying is:
=SUMPRODUCT("IF(INDEX(rngData,,
1)=A18,1,0)*(IF(findctsize2(INDEX(rngData,,2))=10,1,0)*(INDEX(rngData,,
3)))")
...in other words where the count value in column B is 10.
Can anyone suggest what is probably a schoolby error I have made?

My functions are as follows. (There are 2 because my home PC is excel
97 and doesn't have "InstrRev")

Function FindCTSize2(str As String) As Integer
Dim intSpacePos As Integer
Dim intCTPos As Integer
Dim blnCT As Boolean

intCTPos = InStr(1, str, "CT", 1)
If intCTPos > 0 Then
intSpacePos = Findreverse(str, " ")
If intSpacePos > 0 Then
FindCTSize2 = Mid(str, intSpacePos + 1, intCTPos - 1 -
intSpacePos)
Else
FindCTSize2 = Left(str, intCTPos - 1)
End If
End If
End Function

Function Findreverse(s As String, f As String) As Integer

newstring = ""
For x = Len(s) To 1 Step -1
newstring = newstring & Mid(s, x, 1)
Next
If InStr(1, s, " ", 1) > 0 Then
Findreverse = Len(s) - InStr(1, newstring, f) + 1
Else
Findreverse = 0
End If

End Function


Many thanks

John Pomfret

 

[T. Valko]

You don't need to use IF in your formula. Using IF makes it an array entered
formula.

Personally, I wouldn't use a named range and then have to use all those
calls to INDEX, just makes the formula longer.

You don't need a UDF to do this. You just need to add 2 more arrays:

(ISNUMBER(SEARCH("ct",size))
(LEFT(size,2)="10")

Biff

 

[T. Valko]

Ooops! Disregard that last reply.

I just thought of something:

(LEFT(size,2)="10")

There could be entries like this:

10ct
100ct
1000ct

Where the above array would lead to incorrect results.

I'm going out for a few hours but when I return I'll take another look at
this. We should be able to come up with something.

Biff

 

[T. Valko]

Type Size Value
1 10ct 1
2 450gr p 20ct 2
1 3000GR P 100ct 3
2 200GR P 10ct 4
1 10GR P 15ct 5

Can you demonstrate better how the columns are delineated?

What I want to do in addition is to somehow include rows where the
"CT" value (ie what is immediately before CT in column B)

Based on the above sample what is in column B?

Is this all in one cell in column B: 3000GR P 100ct

If that's the case this will be very difficult to accomplish.

Biff

 

[JzP]

Type Size Value
1 10ct 1
2 450gr p 20ct 2
1 3000GR P 100ct 3
2 200GR P 10ct 4
1 10GR P 15ct 5

Can you demonstrate better how the columns are delineated?


Based on the above sample what is in column B?

Is this all in one cell in column B: 3000GR P 100ct

If that's the case this will be very difficult to accomplish.

Biff








- Show quoted text -

Hi Biff,
Thanks for looking at this for me.
Unfortunately column B can contain all the text such as "3000GR P
10CT". In fact there may not be a "CT" in there at all in which case I
need to ignore the row.
I have done a similar formula before where I needed to use some
complicated text scanning but thought that perhaps using a function
would be easier. Obviously not!

All I think I want to do is to somehow get the function to return an
array which I can use in the sumproduct.

If there's no way to do it I'll have to revert to my former, much
messier and long-winded method and try to do it that way.

Thanks.

John

 

[JzP]

Hi Biff,
Thanks for looking at this for me.
Unfortunately column B can contain all the text such as "3000GR P
10CT". In fact there may not be a "CT" in there at all in which case I
need to ignore the row.
I have done a similar formula before where I needed to use some
complicated text scanning but thought that perhaps using a function
would be easier. Obviously not!

All I think I want to do is to somehow get the function to return an
array which I can use in the sumproduct.

If there's no way to do it I'll have to revert to my former, much
messier and long-winded method and try to do it that way.

Thanks.

John- Hide quoted text -

- Show quoted text -

Hi again Biff,
I think I've finally cracked it...
I've rewritten the function to return an array (which I then have to
transpose in the excel formula) and that then seems to feed ok into
the sumproduct.
I have posted my solution here in case anyone else has a similar
problem.
Thanks again for looking at it. That fact encouraged me to keep
working on it.

Cell formula:
=SUMPRODUCT((IF(INDEX(rngData,,1) = 1,1,0)),
(TRANSPOSE(IF(returnSizeCtarray(INDEX(rngData,,2))>10,1,0))),
(INDEX(rngData,,3)))

In this case I get the sum of values where column A is 1 and column B
has a count size > 10.

VBA Code:
Function ReturnSizeCTArray(rngIn As Range)
Dim Arr()
Dim intSpacePos As Integer
Dim intCTPos As Integer
Dim intCTSize As Integer
Dim intLoop As Integer
Dim str As String

ReDim Arr(rngIn.Rows.Count - 1)

For intLoop = 1 To (rngIn.Rows.Count)
str = rngIn(intLoop, 1)
intCTPos = InStr(1, str, "CT", 1)
If intCTPos > 0 Then
intSpacePos = InStrRev(str, " ", , 1)
If intSpacePos > 0 Then
intCTSize = Mid(str, intSpacePos + 1, intCTPos - 1 -
intSpacePos)
Else
intCTSize = Left(str, intCTPos - 1)
End If
Else
intCTSize = 0
End If
Arr(intLoop - 1) = intCTSize
Next intLoop

ReturnSizeCTArray = Arr

End Function

There may be a more efficient way but for the moment - at least this
will keep the client happy!

Cheers

John

 

[T. Valko]

Try using the formula without the IFs (still needs to be array entered
because of TRANSPOSE):

=SUMPRODUCT(--(INDEX(rngData,,1)=1),
--(TRANSPOSE(returnSizeCtarray(INDEX(rngData,,2))>10)),
INDEX(rngData,,3))

Biff