Problem with wildcard in complex formula... help?

[rtilghman]

Having a problem with using wildcards in a formula I wrote. The
formula is:

=IF(AND($A$79<>"",E$99<>""),IF($E$94<>0,SUMPRODUCT((($K$8:$K$74="*"&$A
$79&"*")*($C$8:$C$74="*"&E$99&"*"))/$E$94),0),"")

A79 is a reference value that has an actual text value in it, E99 is a
reference value that has a text value indirectly pulled into it (its
another formula). K8-74 is a range of responses, C8-74 is a separate
range of responses. E94 is the total responses overall.

What I'm doing here is trying to find the matches between two
variables in a bunch of interview responses. Because of how the
responses work I need to be able to wildcard them for the value (some
responses can have multiple entries, and I just need to search for the
term). The problem is taht when I put in the wild card *s or the
values I'm testing have multiple entries (so "a, b, c" and not just
"a") the formula above ceases to function properly.

Thoughts? Based on other formulas I'm using this SHOULD work, and all
the references are solid (I've checked them about 20 times). Very
frustrating... Am I missing something? I have other places where I do
this same function without wildcards and it works, so I have to think
it isn't that the comparison itself is invalid (i.e., it is comparing
the text in A79 with the formula in E99, not the text value that gets
pulled into E99).

Any help is greatly appreciated.

Thanks,
Rick

 

[Bob Phillips]

=IF(AND($A$79<>"",E$99<>""),IF($E$94<>0,
SUMPRODUCT((ISNUMBER(FIND($A$79,$K$8:$K$74)))*
(ISNUMBER(FIND(E$99,$C$8:$C$74)))/$E$94),0),"")

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[rtilghman]

=IF(AND($A$79<>"",E$99<>""),IF($E$94<>0,
SUMPRODUCT((ISNUMBER(FIND($A$79,$K$8:$K$74)))*
(ISNUMBER(FIND(E$99,$C$8:$C$74)))/$E$94),0),"")

Thanks for the corrected formula man, really appreciate it. Found
another couple of threads in the meantime that discussed how wildcards
cannot be used with SumProduct arrays... so frigging annoying.

Okay, so this introduces another wrinkle, maybe you can help? My
original formula was geared for two dimensions, but I had planned on
trying to make it a three dimensional formula for some other
calculations I need to do. The introduction of Find (which I believe
only works in 2D) would appear to put a wrinkle in this plan...

So the question: is there a way to make this formula work in three
dimensions?

Thanks again for the help.

-Rick

 

[rtilghman]

Thanks for the corrected formula man, really appreciate it. Found
another couple of threads in the meantime that discussed how wildcards
cannot be used with SumProduct arrays... so frigging annoying.

Okay, so this introduces another wrinkle, maybe you can help? My
original formula was geared for two dimensions, but I had planned on
trying to make it a three dimensional formula for some other
calculations I need to do. The introduction of Find (which I believe
only works in 2D) would appear to put a wrinkle in this plan...

So the question: is there a way to make this formula work in three
dimensions?

Thanks again for the help.

-Rick

Anyone? A little help?

-rt