Can anyone suggest a simpler Formula? Thanks!

[Satya]

Please look at below long formula. Note that Cell1, Cell2, etc. are different worksheets.

I tried replacing this formula by using
SUMPRODUCT(B1:B40, Cell1:Cell40!F44), or even
SUM(B1:B40 * Cell1:Cell40!F44)

But the formulas don't work, and give me back a REF! error. It seems to be because I am trying to select the second array across multiple worksheets.

ANY HELP IS HIGHLY APPRECIATED!

Formula to be replaced is...

=$B$1*Cell1!F44+$B$2*Cell2!F44+$B$3*Cell3!F44+$B$4*Cell4!F44+$B$5*Cell5!F44+$B$6*Cell6!F44+$B$7*Cell7!F44+$B$8*Cell8!F44+$B$9*Cell9!F44+$B$10*Cell10!F44+$B$11*Cell11!F44+$B$12*Cell12!F44+$B$13*Cell13!F44+$B$14*Cell14!F44+$B$15*Cell15!F44+$B$16*Cell16!F44+$B$17*Cell17!F44+$B$18*Cell18!F44+$B$19*Cell19!F44+$B$20*Cell20!F44+$B$21*Cell21!F44+$B$22*Cell22!F44+$B$23*Cell23!F44+$B$24*Cell24!F44+$B$25*Cell25!F44+$B$26*Cell26!F44+$B$27*Cell27!F44+$B$28*Cell28!F44+$B$29*Cell29!F44+$B$30*Cell30!F44+$B$31*Cell31!F44+$B$32*Cell32!F44+$B$33*Cell33!F44+$B$34*Cell34!F44+$B$35*Cell35!F44+$B$36*Cell36!F44+$B$37*Cell37!F44+$B$38*Cell38!F44+$B$39*Cell39!F44+$B$40*Cell40!F44

 

[santeria]

It may be me being terribly thick, but can you clarify what is going on, or what you want to do?
I see that there are two Sheets, maybe you can separate put in a question the different tasks, and then things can be worked from there ???

Just a thought...

 

[Iridium]

Check out the xl help file on 'Correct a #REF! error' also

HTH

 

[NateO]

Hello,

=SUM(IF(ISNUMBER(N(INDIRECT("Cell"&ROW(1:39)&"!"&ADDRESS(ROW(2:40),2)))),(N(INDIRECT("Cell"&ROW(1:39)&"!"&ADDRESS(ROW(2:40),2))))*(N(INDIRECT("Cell"&ROW(1:39)&"!F44")))))*b1*Cell40!F44

Array entered, ctrl-shift-enter in the formula bar.

I have not tested this, and it is somewhat expensive, probably more so than the the function you posted (while long). I added the if(isnumber) to exclude blank cells. If you have no blanks, you won't need this.

With ref!, you've probably deleted a row, or cut a cell, or something along these lines with a range object that was being pointed to in your function.

 

[Yogi Anand]

Please look at below long formula. Note that Cell1, Cell2, etc. are different worksheets.

I tried replacing this formula by using
SUMPRODUCT(B1:B40, Cell1:Cell40!F44), or even
SUM(B1:B40 * Cell1:Cell40!F44)

But the formulas don't work, and give me back a REF! error. It seems to be because I am trying to select the second array across multiple worksheets.

ANY HELP IS HIGHLY APPRECIATED!

Formula to be replaced is...

=$B$1*Cell1!F44+$B$2*Cell2!F44+$B$3*Cell3!F44+$B$4*Cell4!F44+$B$5*Cell5!F44+$B$6*Cell6!F44+$B$7*Cell7!F44+$B$8*Cell8!F44+$B$9*Cell9!F44+$B$10*Cell10!F44+$B$11*Cell11!F44+$B$12*Cell12!F44+$B$13*Cell13!F44+$B$14*Cell14!F44+$B$15*Cell15!F44+$B$16*Cell16!F44+$B$17*Cell17!F44+$B$18*Cell18!F44+$B$19*Cell19!F44+$B$20*Cell20!F44+$B$21*Cell21!F44+$B$22*Cell22!F44+$B$23*Cell23!F44+$B$24*Cell24!F44+$B$25*Cell25!F44+$B$26*Cell26!F44+$B$27*Cell27!F44+$B$28*Cell28!F44+$B$29*Cell29!F44+$B$30*Cell30!F44+$B$31*Cell31!F44+$B$32*Cell32!F44+$B$33*Cell33!F44+$B$34*Cell34!F44+$B$35*Cell35!F44+$B$36*Cell36!F44+$B$37*Cell37!F44+$B$38*Cell38!F44+$B$39*Cell39!F44+$B$40*Cell40!F44

Hi Satya:

Welcome to MrExcel Board!

Please try ...

=SUMPRODUCT(B1:B40*sum(cell1:cell40!F44))

I hope this helps. If I have misunderstood your question -- my apologies!

 

[Aladin Akyurek]

One of:

1]

=SUMPRODUCT(B1:B40,THREED(Cell1:Cell40!F44))

THREED requires the morefunc.xll add-in.

2]

=SUMPRODUCT(B1:B40,SUBTOTAL(9,INDIRECT("Cell"&ROW(1:40)&"!F44")))

 

[NateO]

Misread, sorry:

{=SUM((N(INDIRECT("Cell"&ROW(1:39)&"!F44"))*(B2:B40)))*b1*Cell40!F44 }

 

[Mat]

Starting from what Nate gave, I think it should be more something like:

{=SUM(N(B1:B40)*N(INDIRECT("Cell"&ROW(1:40)&"!F44")))}

 

[NateO]

Mat, The first and last range, from the original post, appear to stand alone eh.

The only reason I post a non-Morefunc.xll post, is while I can use it, I distribute too much of my stuff to those who don't (for better or worse!).

 

[Mat]

Are you sure Nate?

=$B$1*Cell1!F44+$B$2*Cell2!F44+$B$3*Cell3!F44+$B$4*Cell4!F44+$B$5*Cell5!F44+$B$6*Cell6!F44+$B$7*Cell7!F44+$B$8*Cell8!F44+$B$9*Cell9!F44+$B$10*Cell10!F44+$B$11*Cell11!F44+$B$12*Cell12!F44+$B$13*Cell13!F44+$B$14*Cell14!F44+$B$15*Cell15!F44+$B$16*Cell16!F44+$B$17*Cell17!F44+$B$18*Cell18!F44+$B$19*Cell19!F44+$B$20*Cell20!F44+$B$21*Cell21!F44+$B$22*Cell22!F44+$B$23*Cell23!F44+$B$24*Cell24!F44+$B$25*Cell25!F44+$B$26*Cell26!F44+$B$27*Cell27!F44+$B$28*Cell28!F44+$B$29*Cell29!F44+$B$30*Cell30!F44+$B$31*Cell31!F44+$B$32*Cell32!F44+$B$33*Cell33!F44+$B$34*Cell34!F44+$B$35*Cell35!F44+$B$36*Cell36!F44+$B$37*Cell37!F44+$B$38*Cell38!F44+$B$39*Cell39!F44+$B$40*Cell40!F44