wezred
06-29-2007, 01:13 AM
My column has 10000 combination in each cell of fruits like apple, banana, orange, kiwi. In the next column there are corresponding numbers. For example:
Apple/Banana 10
Apple/Kiwi 12
Kiwi/Orange 30
Orange/Banana 20
Banana/Apple 19
This formula works and sums up all corresponding columns which cells contain the text "apple":
=+SUM(IF(ISNUMBER(SEARCH("Apple",$A$8:$A$10000)),$B$8:$B$10000))
Answer is 41
I tried creating an evaluation function in VBA as I'm planning to use more than 10 criterias, the function looks like this:
Function Txt2F(InputString As String)
Application.Volatile
Txt2F = Evaluate("=(" & InputString & ")")
End Function
However, the following function doesn't work at all:
=+SUM(IF(Txt2F($A$1),Executive!$B$8:$B$10000))
Where cell $A$1 has the text:
ISNUMBER(SEARCH("Apple",$A$8:$A$10000))
I'd like to create an array such that Cell $A$1 has all the criterias, for example:
ISNUMBER(SEARCH("apple",$a$8:$a$10000))*ISNUMBER(SEARCH("banana",$a$8:$a$10000))+ISNUMBER(SEARCH("kiwi",$a$8:$a$10000))
So the function
=+SUM(IF(Txt2F($A$1),Executive!$B$8:$B$10000)) is 71
Anyone here has any ideas or alternatives? I need an evaluation function that works in complex formulas within an array. Thanks.
Apple/Banana 10
Apple/Kiwi 12
Kiwi/Orange 30
Orange/Banana 20
Banana/Apple 19
This formula works and sums up all corresponding columns which cells contain the text "apple":
=+SUM(IF(ISNUMBER(SEARCH("Apple",$A$8:$A$10000)),$B$8:$B$10000))
Answer is 41
I tried creating an evaluation function in VBA as I'm planning to use more than 10 criterias, the function looks like this:
Function Txt2F(InputString As String)
Application.Volatile
Txt2F = Evaluate("=(" & InputString & ")")
End Function
However, the following function doesn't work at all:
=+SUM(IF(Txt2F($A$1),Executive!$B$8:$B$10000))
Where cell $A$1 has the text:
ISNUMBER(SEARCH("Apple",$A$8:$A$10000))
I'd like to create an array such that Cell $A$1 has all the criterias, for example:
ISNUMBER(SEARCH("apple",$a$8:$a$10000))*ISNUMBER(SEARCH("banana",$a$8:$a$10000))+ISNUMBER(SEARCH("kiwi",$a$8:$a$10000))
So the function
=+SUM(IF(Txt2F($A$1),Executive!$B$8:$B$10000)) is 71
Anyone here has any ideas or alternatives? I need an evaluation function that works in complex formulas within an array. Thanks.