Hi Guys,

I have a longish sumproduct formula. One of the conditions inside the
formula references an item in, say, A1 which is "CCC Range" (without the
quotation marks). At the moment I have

LEFT (A1,3)="CCC"

I want the condition to reach CCC+, CCC- and CCC. Something like LEFT
(A1,3)="CCC*" but this doesn't work.

Any ideas?

Thanks in advance

Andrew

Andrew,

You do it like this

=SUMPRODUCT((ISNUMBER(SEARCH("ccc",A1:A20))*(B1:B2 0)))

Mike

"Andrew Mackenzie" wrote:

Hi Guys,

I have a longish sumproduct formula. One of the conditions inside the
formula references an item in, say, A1 which is "CCC Range" (without the
quotation marks). At the moment I have

LEFT (A1,3)="CCC"

I want the condition to reach CCC+, CCC- and CCC. Something like LEFT
(A1,3)="CCC*" but this doesn't work.

Any ideas?

Thanks in advance

Andrew


.

Hmm,

Am I making this more of a problem than it is!! try this

=SUMPRODUCT((LEFT(A1:A15,3)="ccc")*(B1:B15))

Mike


"Mike H" wrote:

Andrew,

You do it like this

=SUMPRODUCT((ISNUMBER(SEARCH("ccc",A1:A20))*(B1:B2 0)))

Mike

"Andrew Mackenzie" wrote:

Hi Guys,

I have a longish sumproduct formula. One of the conditions inside the
formula references an item in, say, A1 which is "CCC Range" (without the
quotation marks). At the moment I have

LEFT (A1,3)="CCC"

I want the condition to reach CCC+, CCC- and CCC. Something like LEFT
(A1,3)="CCC*" but this doesn't work.

Any ideas?

Thanks in advance

Andrew


.

Thanks very much for your help, Mike.

Your first solution worked for the problem as I described it but the formula
also picked up CC+, CC-, C+, CC, C and C-.

The second solution gives me #VALUE. Here is the full formula:

SUMPRODUCT((dceNetFacility)*(dceShamgarInc="Y")*(L EFT(dceWeakestSP="CCC")))

I will also need formulas for CC and C (in each case including the "+" and
"-").

Any further assistance appreciated.

Cheers,

Andrew


"Mike H" wrote in message
...
Hmm,

Am I making this more of a problem than it is!! try this

=SUMPRODUCT((LEFT(A1:A15,3)="ccc")*(B1:B15))

Mike


"Mike H" wrote:

Andrew,

You do it like this

=SUMPRODUCT((ISNUMBER(SEARCH("ccc",A1:A20))*(B1:B2 0)))

Mike

"Andrew Mackenzie" wrote:

Hi Guys,

I have a longish sumproduct formula. One of the conditions inside the
formula references an item in, say, A1 which is "CCC Range" (without
the
quotation marks). At the moment I have

LEFT (A1,3)="CCC"

I want the condition to reach CCC+, CCC- and CCC. Something like LEFT
(A1,3)="CCC*" but this doesn't work.

Any ideas?

Thanks in advance

Andrew


.

Using the multiplication operation in this syntax:
=SUMPRODUCT((LEFT(A1:A15,3)="ccc")*(B1:B15))

Will cause that #value error if there are non-numbers in B1:B15.

You could use this alternative syntax that will ignore text (treat it as 0) in
B1:B15:

=SUMPRODUCT(--(LEFT(A1:A15,3)="ccc"),B1:B15)

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

==================
If you have #value errors in either range, you can get this error, too.

Andrew Mackenzie wrote:

Thanks very much for your help, Mike.

Your first solution worked for the problem as I described it but the formula
also picked up CC+, CC-, C+, CC, C and C-.

The second solution gives me #VALUE. Here is the full formula:

SUMPRODUCT((dceNetFacility)*(dceShamgarInc="Y")*(L EFT(dceWeakestSP="CCC")))

I will also need formulas for CC and C (in each case including the "+" and
"-").

Any further assistance appreciated.

Cheers,

Andrew

"Mike H" wrote in message
...
Hmm,

Am I making this more of a problem than it is!! try this

=SUMPRODUCT((LEFT(A1:A15,3)="ccc")*(B1:B15))

Mike


"Mike H" wrote:

Andrew,

You do it like this

=SUMPRODUCT((ISNUMBER(SEARCH("ccc",A1:A20))*(B1:B2 0)))

Mike

"Andrew Mackenzie" wrote:

Hi Guys,

I have a longish sumproduct formula. One of the conditions inside the
formula references an item in, say, A1 which is "CCC Range" (without
the
quotation marks). At the moment I have

LEFT (A1,3)="CCC"

I want the condition to reach CCC+, CCC- and CCC. Something like LEFT
(A1,3)="CCC*" but this doesn't work.

Any ideas?

Thanks in advance

Andrew


.


--

Dave Peterson