Ok, maybe I misunderstood...
Now I agree.
When you have multiple conditions, so you can't use VLOOKUP, COUNTIF or
SUMIF, and if the expected return value is text, you cannot absolutely use
SUMPRODUCT but you must use INDEX + MATCH array-entered.
To avoid error value you could write SUMPRODOCUT in this way:
=SUMPRODUCT((rng1=cond1)*(rng2=cond2),(rng3))
In this way if rng3 should have a single occurrence of a text value like a
"" there would be no error and if rng3 should be a text range the result
value would be 0.
Nel post *Peo Sjoblom* ha scritto:
I was not talking about the criteria ranges but what will be the
result of the formula, you will never get sumproduct to return a
text value, I am not talking about the 2 criteria ranges in your
formula, I am talking about the quantity range which obviously will
work in this case since it is a number, however if this range
Sheet2!$C$2:$C$14 held text values your formula will return a value
error, it even will return a value error if there is a single
occurrence of a text value like a "" from a formula. Using index with
match can only return a #N/A error and that would be if there is no
match
--
(I'm not sure of names of menus, options and commands, because
translating from the Italian version of Excel...)
Hope I helped you.
Thanks in advance for your feedback.
Ciao
Franz Verga from Italy