Bernard, your post got me thinking ... about the uneven 18 element and 9
elements arrays.
FWIW,
Actually, looking closely at the arrays displayed when <F9 is hit, you see
only 9 semi-colons in *both* arrays, where A1 to A9 also shows 9 commas.
This means that both arrays are 9 rows in size, while Col A is 9 rows by 2
columns.
So, multiplying row times row returns the answers for the multiplication
operator.
I don't know what happens with the comma in the unary version.
If you replace the comma with an asterisk, forcing a second multiplication
(unary performing the first), you get the same correct return.

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Bernard Liengme" wrote in message
...
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