Thanks for this, We life and learn
cheers
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"RagDyer" wrote in message
...
If you've seen my posts over the years, you might have noticed my
*distaste* for the unary form of Sumproduct.

Besides the fact that it *can't* be used on different sized ranges (column
vs. row), and the fact that it allows text numbers to be by-passed without
any notice, it *also* doesn't work in this scenario.

Just revise your test formula from unary to asterisk, which is the form my
suggested formula used.

=SUMPRODUCT((A1:A9={"A","B"})*B1:B9)

No problem with this ... is there?
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Bernard Liengme" wrote in message
...
That will earn a #VALUE! error
Let A1:A9 have letters a, b or c in each cell
Let B1:B9 have numbers
Then your method becomes =SUMPRODUCT(--(A1:A9={"A","B"}),B1:B9)
But the part (A1:A9={"A","B"}) generates an 18 element array of TRUE and
FALSE values. But since B1:B9 is a nine element array, we have a
mismatch.

T Valko's method with ISNUMBER(MATCH overcomes the problem and let one
use {"A","B"}

best wishes from Canada
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"RagDyer" wrote in message
...
Little shorter:

=SUMPRODUCT(('M:\WORKBOOK1\[SHEET1]SHEET1'!$A$7:$A$70={"INT","IT","MA"})
*'M:\WORKBOOK1\[SHEET1]SHEET1'!$AK$7:$AK$70)

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit
!
---------------------------------------------------------------------------
"torooo" wrote in message
...
Thanks for your assistance, the formula works (without the extra
parenthesis).

Best