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