Multiply many non-contiguous cells

[KENNY]

Hello,

I have two worksheet with the same layout:

A B C D E F
10 22 14 15 11 77

I would like to: (Sheet1!A1*Sheet2!A1) + Sum(Sheet1!
C1*Sheet2!C1)+ Sum(Sheet1!E1*Sheet2!E1 )

I'm skipping columns so an array is out, yes? Thanks!

 

[Frank Kabel]

Hi
try:
=SUMPRODUCT(Sheet1!A1:X1,Sheet2!A1:x1,--(MOD(COLUMN(Sheet1!A1:X1),2)=1)
)

 

[KENNY]

Great! What if I simply want to add the contents of every
other column (below)?
Thanks

 

[Domenic]

=SUMPRODUCT((Sheet1!A1:F1+Sheet2!A1:F1),--(MOD(COLUMN(Sheet2!A1:F1),2)=1)
)

Adjust the range accordingly.

Hope this helps!

 

[KENNY]

Thanks! Can you put the formula in laymen's term? I've
never seen the use of dashes before (--), and I don't see
it anywhere in Excel's HELP area

 

[Domenic]

Thanks! Can you put the formula in laymen's term? I've
never seen the use of dashes before (--), and I don't see
it anywhere in Excel's HELP area

Have a look at Aladin's explanation here...

http://www.mrexcel.com/board2/viewtopic.php?t=103530

 

[Gord Dibben]

Kenny

See some info on double unary here.

http://snipurl.com/9k62

Gord Dibben Excel MVP