Using UDF in SUMPRODUCT
 

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

Using UDF in SUMPRODUCT
 

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

"JzP" wrote in message
oups.com...
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

Using UDF in SUMPRODUCT
 

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" wrote in message
...
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

"JzP" wrote in message
oups.com...
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

Using UDF in SUMPRODUCT
 

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

"T. Valko" wrote in message
...
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" wrote in message
...
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

"JzP" wrote in message
oups.com...
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

Using UDF in SUMPRODUCT
 

On Apr 23, 3:18 am, "T. Valko" wrote:
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

"T. Valko" wrote in message

...



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" wrote in message
...
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

"JzP" wrote in message
groups.com...
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- Hide quoted text -

- 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

Using UDF in SUMPRODUCT
 

On Apr 23, 7:35 am, JzP wrote:
On Apr 23, 3:18 am, "T. Valko" wrote:





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

"T. Valko" wrote in message

...

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" wrote in message
...
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

"JzP" wrote in message
groups.com...
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- Hide quoted text -

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

Using UDF in SUMPRODUCT
 

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

"JzP" wrote in message
oups.com...
On Apr 23, 7:35 am, JzP wrote:
On Apr 23, 3:18 am, "T. Valko" wrote:





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

"T. Valko" wrote in message

...

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" wrote in message
...
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

"JzP" wrote in message
groups.com...
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- Hide quoted text -

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