Wild card quaestion

  • Thread starter Thread starter Andrew Mackenzie
  • Start date Start date
A

Andrew Mackenzie

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
 
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")*(LEFT(dceWeakestSP="CCC")))

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

Any further assistance appreciated.

Cheers,

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 here:
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.
 
Back
Top