I would like to replace the formula below with SUMPRODUCT:

=SUM(IF(('M:\WORKBOOK1\SHEET1'!$A$7:$A$70="INT")+( 'M:
\WORKBOOK1\SHEET1'!$A$7:$A$70="IT")+('M:WORKBOOK1\ SHEET1'$A$7:$A
$70="MA"),'M:\WORKBOOK1\SHEET1'!"$AK$7:$AK$70))


Thanks in advance for your assitance.

=SUMPRODUCT((('M:\WORKBOOK1\SHEET1'!$A$7:$A$70="IN T")+('M:
\WORKBOOK1\SHEET1'!$A$7:$A$70="IT")+('M:WORKBOOK1\ SHEET1'$A$7:$A$70="MA")),'M:\WORKBOOK1\SHEET1'!"$A K$7:$AK$70))
UNTESTED
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"torooo" wrote in message
...
I would like to replace the formula below with SUMPRODUCT:

=SUM(IF(('M:\WORKBOOK1\SHEET1'!$A$7:$A$70="INT")+( 'M:
\WORKBOOK1\SHEET1'!$A$7:$A$70="IT")+('M:WORKBOOK1\ SHEET1'$A$7:$A
$70="MA"),'M:\WORKBOOK1\SHEET1'!"$AK$7:$AK$70))


Thanks in advance for your assitance.

There is an extra parenthesis at the end. Other than that, that's the same as
what I got. Just remember to enter it as an array. (Ctrl+Shift+Enter)
--
Best Regards,

Luke M


"Bernard Liengme" wrote:

=SUMPRODUCT((('M:\WORKBOOK1\SHEET1'!$A$7:$A$70="IN T")+('M:
\WORKBOOK1\SHEET1'!$A$7:$A$70="IT")+('M:WORKBOOK1\ SHEET1'$A$7:$A$70="MA")),'M:\WORKBOOK1\SHEET1'!"$A K$7:$AK$70))
UNTESTED
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"torooo" wrote in message
...
I would like to replace the formula below with SUMPRODUCT:

=SUM(IF(('M:\WORKBOOK1\SHEET1'!$A$7:$A$70="INT")+( 'M:
\WORKBOOK1\SHEET1'!$A$7:$A$70="IT")+('M:WORKBOOK1\ SHEET1'$A$7:$A
$70="MA"),'M:\WORKBOOK1\SHEET1'!"$AK$7:$AK$70))


Thanks in advance for your assitance.

Thanks for your assistance, the formula works (without the extra
parenthesis).

Best

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

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

Just remember to enter it as an array. (Ctrl+Shift+Enter)

No need to array enter.

--
Biff
Microsoft Excel MVP


"Luke M" wrote in message
...
There is an extra parenthesis at the end. Other than that, that's the same
as
what I got. Just remember to enter it as an array. (Ctrl+Shift+Enter)
--
Best Regards,

Luke M


"Bernard Liengme" wrote:

=SUMPRODUCT((('M:\WORKBOOK1\SHEET1'!$A$7:$A$70="IN T")+('M:
\WORKBOOK1\SHEET1'!$A$7:$A$70="IT")+('M:WORKBOOK1\ SHEET1'$A$7:$A$70="MA")),'M:\WORKBOOK1\SHEET1'!"$A K$7:$AK$70))
UNTESTED
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"torooo" wrote in message
...
I would like to replace the formula below with SUMPRODUCT:

=SUM(IF(('M:\WORKBOOK1\SHEET1'!$A$7:$A$70="INT")+( 'M:
\WORKBOOK1\SHEET1'!$A$7:$A$70="IT")+('M:WORKBOOK1\ SHEET1'$A$7:$A
$70="MA"),'M:\WORKBOOK1\SHEET1'!"$AK$7:$AK$70))


Thanks in advance for your assitance.

Another way:

=SUMPRODUCT(--(ISNUMBER(MATCH('M:\WORKBOOK1\SHEET1'!$A$7:$A$70,{ "INT","IT","MA"),0))),'M:\WORKBOOK1\SHEET1'!"$AK$7 :$AK$70)

--
Biff
Microsoft Excel MVP


"torooo" wrote in message
...
I would like to replace the formula below with SUMPRODUCT:

=SUM(IF(('M:\WORKBOOK1\SHEET1'!$A$7:$A$70="INT")+( 'M:
\WORKBOOK1\SHEET1'!$A$7:$A$70="IT")+('M:WORKBOOK1\ SHEET1'$A$7:$A
$70="MA"),'M:\WORKBOOK1\SHEET1'!"$AK$7:$AK$70))


Thanks in advance for your assitance.