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