Hi There,

I am trying to make a sumproduct work ...with a limited list of
criteria.

Underneath Sumproduct works fine ... as you can use wildcharacters etc,
but that's just the down-side of it as well ..in a long range too many
Items from my range might qualify.
=SUMPRODUCT(--(ISNUMBER(SEARCH(A1;G1:G1750)));--(H1:H1750))
eg: A1 = *si*
would trigger on: sSIGE, SSIG,maxsi, etc etc


Therefore I am more looking for a sumproduct function where I can
specify a range of
text strings that might appear in my range to be evaluated
(G1:G1750)...

Something like:
=SUMPRODUCT(--(ISNUMBER(MATCH(G1:G1750;$A$1:$A$6;0)));--(H1:H1750))
But here the problem is that it wil trigger only on the EXACT
resemblance of the condition cells versus the range to be evaluated;
ie: A1= SIGE triggers only on "SIGE" (& variances with small/ capital
letters) but not on "xSIGEy"

So it should be a function that triggers on the exact text strings in
my list BUT these text strings could be "within" the
cells-to-be-evaluated.
eg.
A1 = "SIG" should trigger on "KSIGE" but should not on "sige"
A2= "sige" should not trigger on "KSIGE" but should on "sad_sige_"

I hope you understand my drift ...

I was thinking of something like:
=SUMPRODUCT(--(ISNUMBER(FIND(A1:A6;G1:G1750)));--(H1:H1750))
as the FIND is case-sensitive ...but it only looks for a single
Character.

So it obviously does not suit my purposes

Hi Sige
FIND is case-sensitive ...but it only looks for a single Character.

Don't understand this. Yes it is case sensitive, but it works for any
number of characters - the same as SEARCH
It appears to return the correct results for me.


--
Regards

Roger Govier


"Sige" wrote in message
oups.com...
I was thinking of something like:
=SUMPRODUCT(--(ISNUMBER(FIND(A1:A6;G1:G1750)));--(H1:H1750))
as the FIND is case-sensitive ...but it only looks for a single
Character.

So it obviously does not suit my purposes

Hi Roger,

=SUMPRODUCT(--(ISNUMBER(FIND(A1:A6;G1:G20)));--(H1:H20))

In my practical example I can tell ... the results are not as they
should.
If A1 = Sige (mind the small caps) (not even bothering the other
criteria...)

My range G1:G20 = SIGE1 till SIGE20 (mind the CAPITALS)
It returns a correct result?

Hi Sige

Perhaps I'm misunderstanding you.
With A1:A6 containing Sige, SIGE, sIGE, SigE, sIGe, sIgE
With G1:G20 containing SIGE1 - SIGE20
then with
=SUMPRODUCT(--(ISNUMBER(FIND(A1:A6;G1:G20)))
I return the correct result of 1.
The rest of the formula is immaterial to this argument.

--
Regards

Roger Govier


"Sige" wrote in message
oups.com...
Hi Roger,

=SUMPRODUCT(--(ISNUMBER(FIND(A1:A6;G1:G20)));--(H1:H20))

In my practical example I can tell ... the results are not as they
should.
If A1 = Sige (mind the small caps) (not even bothering the other
criteria...)

My range G1:G20 = SIGE1 till SIGE20 (mind the CAPITALS)
It returns a correct result?

Hi Roger,

In your example: With A2= SIGE & G1-G20 =SIGE1-20
It should return 20 ... finding SIGE (case-sensitive), as there are 20
values in the lookup range which contain the "SIGE"string.
Doing a case-sensitve find, on a range of textstrings ...which might
appear in another range.


Hope you can help,
Sige



Roger Govier wrote:
Hi Sige

Perhaps I'm misunderstanding you.
With A1:A6 containing Sige, SIGE, sIGE, SigE, sIGe, sIgE
With G1:G20 containing SIGE1 - SIGE20
then with
=SUMPRODUCT(--(ISNUMBER(FIND(A1:A6;G1:G20)))
I return the correct result of 1.
The rest of the formula is immaterial to this argument.

--
Regards

Roger Govier


"Sige" wrote in message
oups.com...
Hi Roger,

=SUMPRODUCT(--(ISNUMBER(FIND(A1:A6;G1:G20)));--(H1:H20))

In my practical example I can tell ... the results are not as they
should.
If A1 = Sige (mind the small caps) (not even bothering the other
criteria...)

My range G1:G20 = SIGE1 till SIGE20 (mind the CAPITALS)
It returns a correct result?

=SUMPRODUCT(--(ISNUMBER(SEARCH($A$1:$A$6;G1:G1750)));--(H1:H1750))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Sige" wrote in message
oups.com...
Hi There,

I am trying to make a sumproduct work ...with a limited list of
criteria.

Underneath Sumproduct works fine ... as you can use wildcharacters etc,
but that's just the down-side of it as well ..in a long range too many
Items from my range might qualify.
=SUMPRODUCT(--(ISNUMBER(SEARCH(A1;G1:G1750)));--(H1:H1750))
eg: A1 = *si*
would trigger on: sSIGE, SSIG,maxsi, etc etc


Therefore I am more looking for a sumproduct function where I can
specify a range of
text strings that might appear in my range to be evaluated
(G1:G1750)...

Something like:
=SUMPRODUCT(--(ISNUMBER(MATCH(G1:G1750;$A$1:$A$6;0)));--(H1:H1750))
But here the problem is that it wil trigger only on the EXACT
resemblance of the condition cells versus the range to be evaluated;
ie: A1= SIGE triggers only on "SIGE" (& variances with small/ capital
letters) but not on "xSIGEy"

So it should be a function that triggers on the exact text strings in
my list BUT these text strings could be "within" the
cells-to-be-evaluated.
eg.
A1 = "SIG" should trigger on "KSIGE" but should not on "sige"
A2= "sige" should not trigger on "KSIGE" but should on "sad_sige_"

I hope you understand my drift ...

Sorry, that doesn't work, and I don't have time at present to sort it.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Bob Phillips" wrote in message
...
=SUMPRODUCT(--(ISNUMBER(SEARCH($A$1:$A$6;G1:G1750)));--(H1:H1750))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Sige" wrote in message
oups.com...
Hi There,

I am trying to make a sumproduct work ...with a limited list of
criteria.

Underneath Sumproduct works fine ... as you can use wildcharacters etc,
but that's just the down-side of it as well ..in a long range too many
Items from my range might qualify.
=SUMPRODUCT(--(ISNUMBER(SEARCH(A1;G1:G1750)));--(H1:H1750))
eg: A1 = *si*
would trigger on: sSIGE, SSIG,maxsi, etc etc


Therefore I am more looking for a sumproduct function where I can
specify a range of
text strings that might appear in my range to be evaluated
(G1:G1750)...

Something like:
=SUMPRODUCT(--(ISNUMBER(MATCH(G1:G1750;$A$1:$A$6;0)));--(H1:H1750))
But here the problem is that it wil trigger only on the EXACT
resemblance of the condition cells versus the range to be evaluated;
ie: A1= SIGE triggers only on "SIGE" (& variances with small/ capital
letters) but not on "xSIGEy"

So it should be a function that triggers on the exact text strings in
my list BUT these text strings could be "within" the
cells-to-be-evaluated.
eg.
A1 = "SIG" should trigger on "KSIGE" but should not on "sige"
A2= "sige" should not trigger on "KSIGE" but should on "sad_sige_"

I hope you understand my drift ...