Sumproduct across multiple worksheets

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

woodcockmolely - 24 Aug 2006 17:51 GMT

I have a series of identical worksheets from First! to Last!. Is it possible
to sum the product of cell A1 and cell B1 in each of the workbooks? If so,
how? Thanks

Reply to this Message

woodcockmolely - 24 Aug 2006 18:15 GMT

I should add that I am trying to avoid setting up a product (ie C1=A1*B1) in
individual worksheets.

> I have a series of identical worksheets from First! to Last!. Is it possible
> to sum the product of cell A1 and cell B1 in each of the workbooks? If so,
> how? Thanks

Reply to this Message

Bob Phillips - 24 Aug 2006 18:44 GMT

Put the sheet names in cells M1:M3 (or further) and use

=SUMPRODUCT(N(INDIRECT(M1:M3&"!A1")),N(INDIRECT(M1:M3&"!B1")))

Signature

HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

> I should add that I am trying to avoid setting up a product (ie C1=A1*B1) in
> individual worksheets.
>
> > I have a series of identical worksheets from First! to Last!. Is it possible
> > to sum the product of cell A1 and cell B1 in each of the workbooks? If so,
> > how? Thanks

Reply to this Message

woodcockmolely - 30 Aug 2006 09:28 GMT

Many thanks.
This fixes the cell references, but I found that if I used

=SUMPRODUCT(N(INDIRECT($M$1:$M$3&"!"&TEXT(ADDRESS(ROW(A1),COLUMN(A1),4),0))),N(INDIRECT(($M$1:$M$3&"!B1"))))

then I could copy the formula to cells A2,A3 etc with automatic change of
the reference to cell A1 leaving B1 unchanged.

> Put the sheet names in cells M1:M3 (or further) and use
>
[quoted text clipped - 9 lines]
> so,
> > > how? Thanks

Reply to this Message