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