SumProduct with criteria list
 

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 ...

SumProduct with criteria list
 

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

SumProduct with criteria list
 

=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 ...

SumProduct with criteria list
 

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 ...

SumProduct with criteria list
 

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

SumProduct with criteria list
 

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 with criteria list
 

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 with criteria list
 

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 with criteria list
 

If you are only looking at one cell

=SUMPRODUCT(--(ISNUMBER(FIND(A2,G1:G20))))

returns 20.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Sige" wrote in message
oups.com...

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 with criteria list
 

Hi Bob,

That one is perfectly OK ... though for a set of conditions?

I would like to avoid to write a formula like:
=SUMPRODUCT(--(ISNUMBER(FIND(A1,G1:G20))))
+SUMPRODUCT(--(ISNUMBER(FIND(A2,G1:G20))))
+SUMPRODUCT(--(ISNUMBER(FIND(A3,G1:G20))))
+SUMPRODUCT(--(ISNUMBER(FIND(A3,G1:G20))))
+SUMPRODUCT(--(ISNUMBER(FIND(A4,G1:G20))))
+SUMPRODUCT(--(ISNUMBER(FIND(A5,G1:G20))))
+SUMPRODUCT(--(ISNUMBER(FIND(A6,G1:G20)))) +...


I might have like 20-25 conditions ...
Best Regards, Sige

SumProduct with criteria list
 

Hi Bob,

Is it possible to see which values in range G1:G20 qualify to
=SUMPRODUCT(--(ISNUMBER(FIND(A2,G1:G20)))) with conditional formatting?

Crossing fingers,
Sige

SumProduct with criteria list
 

It will have to be this i guess ...
=SUMPRODUCT(--(ISNUMBER(FIND($A$1;$G1))))

SumProduct with criteria list
 

How about

=SUMPRODUCT(COUNTIF(G1:G20,A1:A6&"*"))

not for CF, but for the original problem.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Sige" wrote in message
oups.com...
It will have to be this i guess ...
=SUMPRODUCT(--(ISNUMBER(FIND($A$1;$G1))))

SumProduct with criteria list
 

Hi Bob,

Thanks a lot ... nearly there ;o)
-There is no way in getting it case-sensitive?
-The formula is properly counting the items in the range qualying to
the "6" conditions, but summing another column based on these criteria?

=SUMPRODUCT(--(ISNUMBER(COUNTIF(G1:G20;A1:A6&"*")));--(H1:H20))
Does not do the trick...

Best Regards, Sige




Bob Phillips wrote:
How about

=SUMPRODUCT(COUNTIF(G1:G20,A1:A6&"*"))

not for CF, but for the original problem.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Sige" wrote in message
oups.com...
It will have to be this i guess ...
=SUMPRODUCT(--(ISNUMBER(FIND($A$1;$G1))))

SumProduct with criteria list
 

Hi Sige

With Bob's formula, I return 120 as the result, with SIGE1 through
SIGE20 in G1:G20, whether I enter just SIGE in A1 and leave the
remainder blank, or if I enter SIGE in various combinations of upper
and lower case in all cells A1:A6.

If there are no blank cells in the range A1:A6, and SIGE appears only
once (however it is typed), then I get the answer of 20

With your amendment (excluding the H1:H20 part for the moment), I get
the answer 6 in all cases

I don't have a solution for you, as yet, and I am not trying to be
negative about any of the suggestions posted thus far.
I have still been thinking about this problem during idle moments<bg

--
Regards

Roger Govier



Sige wrote:
Hi Bob,

Thanks a lot ... nearly there ;o)
-There is no way in getting it case-sensitive?
-The formula is properly counting the items in the range qualying to
the "6" conditions, but summing another column based on these
criteria?

=SUMPRODUCT(--(ISNUMBER(COUNTIF(G1:G20;A1:A6&"*")));--(H1:H20))
Does not do the trick...

Best Regards, Sige




Bob Phillips wrote:
How about

=SUMPRODUCT(COUNTIF(G1:G20,A1:A6&"*"))

not for CF, but for the original problem.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Sige" wrote in message
oups.com...
It will have to be this i guess ...
=SUMPRODUCT(--(ISNUMBER(FIND($A$1;$G1))))

SumProduct with criteria list
 

Hi Roger ,


With Bob's formula, I return 120 as the result, with SIGE1 through
SIGE20 in G1:G20, whether I enter just SIGE in A1 and leave the
remainder blank,

Thats right, but the remaining blank conditions could be easily
replaced with eg. "-", so that's not the issue.

or if I enter SIGE in various combinations of upper
and lower case in all cells A1:A6.
It is not case-sensitive and therefore adds the occurences of each
individual "condition". If you are just putting variations of "sige" in
range A1:A6 with the mentioned G1:G20 range then you have: 6*20
conditions = 120 ,with "sige" in A1:A5 and A6 ="-" you''ll get 100.


I don't have a solution for you, as yet, and I am not trying to be
negative about any of the suggestions posted thus far.
I have still been thinking about this problem during idle moments<bg

I hope I have not been negative neither ... !!!
I feel I cannot solve this particular issue myself. Therefore I came to
ask some EXPERT help... feeling bad to come back to you, as your
proposals are not doing yet what I hope it should.
Grateful for your and Bob's proposals!

Hoping you will find idle time, :o)

Best Regards,
Sige



Roger Govier wrote:
Hi Sige

With Bob's formula, I return 120 as the result, with SIGE1 through
SIGE20 in G1:G20, whether I enter just SIGE in A1 and leave the
remainder blank, or if I enter SIGE in various combinations of upper
and lower case in all cells A1:A6.

If there are no blank cells in the range A1:A6, and SIGE appears only
once (however it is typed), then I get the answer of 20

With your amendment (excluding the H1:H20 part for the moment), I get
the answer 6 in all cases

I don't have a solution for you, as yet, and I am not trying to be
negative about any of the suggestions posted thus far.
I have still been thinking about this problem during idle moments<bg

--
Regards

Roger Govier



Sige wrote:
Hi Bob,

Thanks a lot ... nearly there ;o)
-There is no way in getting it case-sensitive?
-The formula is properly counting the items in the range qualying to
the "6" conditions, but summing another column based on these
criteria?

=SUMPRODUCT(--(ISNUMBER(COUNTIF(G1:G20;A1:A6&"*")));--(H1:H20))
Does not do the trick...

Best Regards, Sige




Bob Phillips wrote:
How about

=SUMPRODUCT(COUNTIF(G1:G20,A1:A6&"*"))

not for CF, but for the original problem.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Sige" wrote in message
oups.com...
It will have to be this i guess ...
=SUMPRODUCT(--(ISNUMBER(FIND($A$1;$G1))))

SumProduct with criteria list
 

Sige,

Can you give us an example, the data in A1:A6, G1:G20, H1:H20 and what
result you expect.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

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


With Bob's formula, I return 120 as the result, with SIGE1 through
SIGE20 in G1:G20, whether I enter just SIGE in A1 and leave the
remainder blank,

Thats right, but the remaining blank conditions could be easily
replaced with eg. "-", so that's not the issue.

or if I enter SIGE in various combinations of upper
and lower case in all cells A1:A6.
It is not case-sensitive and therefore adds the occurences of each
individual "condition". If you are just putting variations of "sige" in
range A1:A6 with the mentioned G1:G20 range then you have: 6*20
conditions = 120 ,with "sige" in A1:A5 and A6 ="-" you''ll get 100.


I don't have a solution for you, as yet, and I am not trying to be
negative about any of the suggestions posted thus far.
I have still been thinking about this problem during idle moments<bg

I hope I have not been negative neither ... !!!
I feel I cannot solve this particular issue myself. Therefore I came to
ask some EXPERT help... feeling bad to come back to you, as your
proposals are not doing yet what I hope it should.
Grateful for your and Bob's proposals!

Hoping you will find idle time, :o)

Best Regards,
Sige



Roger Govier wrote:
Hi Sige

With Bob's formula, I return 120 as the result, with SIGE1 through
SIGE20 in G1:G20, whether I enter just SIGE in A1 and leave the
remainder blank, or if I enter SIGE in various combinations of upper
and lower case in all cells A1:A6.

If there are no blank cells in the range A1:A6, and SIGE appears only
once (however it is typed), then I get the answer of 20

With your amendment (excluding the H1:H20 part for the moment), I get
the answer 6 in all cases

I don't have a solution for you, as yet, and I am not trying to be
negative about any of the suggestions posted thus far.
I have still been thinking about this problem during idle moments<bg

--
Regards

Roger Govier



Sige wrote:
Hi Bob,

Thanks a lot ... nearly there ;o)
-There is no way in getting it case-sensitive?
-The formula is properly counting the items in the range qualying to
the "6" conditions, but summing another column based on these
criteria?

=SUMPRODUCT(--(ISNUMBER(COUNTIF(G1:G20;A1:A6&"*")));--(H1:H20))
Does not do the trick...

Best Regards, Sige




Bob Phillips wrote:
How about

=SUMPRODUCT(COUNTIF(G1:G20,A1:A6&"*"))

not for CF, but for the original problem.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Sige" wrote in message
oups.com...
It will have to be this i guess ...
=SUMPRODUCT(--(ISNUMBER(FIND($A$1;$G1))))

SumProduct with criteria list
 

Hi Bob,

A1:A6 = {Milk;MILK1;MILK2;MILK3;MILK4;@}

G1:G20 ={Milk1;Milk2;Milk3;Milk4;Milk5;* Milk6; *Milk7;Milk8
;Milk9_Bob;Bob_Milk10;MILK1;MILK1;MILK1;MILK2;MILK 2;MILK2_BOB;MILK7;MILK8;MILKYWAY;Assi-MilKnowledge}

H1:H20= {1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;2 0}

I would like it to return: 136
Whe
A1 = "Milk" to qualify for the first 10 items (G1:G10), totalling
H1:H10 = 55
A2= "MILK1" to qualify for G11:G13 totalling H11:H13 =36
A3= "MILK2" for G14:G16 totalling H14:H16 = 45
A4 not qualifying
A5 not qualifying
A6 not qualifying

IMO: I cannot do it with

=SUMPRODUCT(--(ISNUMBER(SEARCH(A1;G1:G20)));--(H1:H20))
with A1= "Milk"as it will sum all, also the one I do NOT want to sum
because they are with CAPITALS.

Looking for a CASE-sensitive function, one (I) would go for FIND, but
htis function does not allow wildcard characters, so I would need to
set up a limited list of conditions to meet:
Spontaneously I would hope:
=SUMPRODUCT(--(ISNUMBER(FIND(A1:A6;G1:G20)));--(H1:H20)) that this
would resolve my problem.
But .....

I hope this example makes it clearer.
Best Regards, Sige

PS: my real example has a couple 000 rows, so I would need a more
extended criteria-list as well..