SUMPRODUCT

[Gary Thomson]

Yesterday I posted the question:

Is there a quicker way of summing the multiplication of 2
values in each sheet over a number of sheets?

i.e.
=Sheet1!B2*Sheet1!C2+Sheet2!B2*Sheet2!C2+Sheet3!B2*Sheet3!
C2+Sheet4!B2*Sheet4!C2+Sheet5!B2*Sheet5!C2+...+Sheet29!
B2*Sheet29!C2+Sheet30!B2*Sheet30!C2


And i got the answer:

=SUMPRODUCT(N(INDIRECT("Sheet"&{1,2,...,30}&"!B2")),
N(INDIRECT("Sheet"&{1,2,...,30}&"!C2")))


Which works!!!

But suppose I have the following:

Sheet1:

B2: a
C2: £10


Sheet2:

B2: b
C2: £11


Sheet3:

B2:
C2: £15

and so on up to sheet 30.

Now if B2 contains text, this acts as a "1", and if not,
then it acts as a zero. So the formula for the above
would be:

=1*10 + 1*11 + 0*15
=21

At the moment, I currently have this as the following
formula:

=COUNTA(Sheet1!B2)*Sheet1!C2+COUNTA(Sheet2!B2)*Sheet2!
C2+COUNTA(Sheet3!B2)*Sheet3!C2+...+COUNTA(Sheet30!B2)
*Sheet30!C2

How could I make this a SUMPRODUCT, or something else that
is shorter?

 

[Arvi Laanemets]

Hi

When sheets Sheet1...Sheet30 are in contionous range (no other sheet tabs
between them), then add a column with product (pe.e. X, X2=B2*C2, it
doesn't take much time really - a copy-paste thing) into every sheet (you
can hide that column then), and use the formula

=SUM(Sheet1:Sheet30!X2)


Arvi Laanemets


Yesterday I posted the question:

Is there a quicker way of summing the multiplication of 2
values in each sheet over a number of sheets?

i.e.
=Sheet1!B2*Sheet1!C2+Sheet2!B2*Sheet2!C2+Sheet3!B2*Sheet3!
C2+Sheet4!B2*Sheet4!C2+Sheet5!B2*Sheet5!C2+...+Sheet29!
B2*Sheet29!C2+Sheet30!B2*Sheet30!C2


And i got the answer:

=SUMPRODUCT(N(INDIRECT("Sheet"&{1,2,...,30}&"!B2")),
N(INDIRECT("Sheet"&{1,2,...,30}&"!C2")))


Which works!!!

But suppose I have the following:

Sheet1:

B2: a
C2: £10


Sheet2:

B2: b
C2: £11


Sheet3:

B2:
C2: £15

and so on up to sheet 30.

Now if B2 contains text, this acts as a "1", and if not,
then it acts as a zero. So the formula for the above
would be:

=1*10 + 1*11 + 0*15
=21

At the moment, I currently have this as the following
formula:

=COUNTA(Sheet1!B2)*Sheet1!C2+COUNTA(Sheet2!B2)*Sheet2!
C2+COUNTA(Sheet3!B2)*Sheet3!C2+...+COUNTA(Sheet30!B2)
*Sheet30!C2

How could I make this a SUMPRODUCT, or something else that
is shorter?

 

[Gary Thomson]

This is difficult, as I have one fixed cell (which will be
B2, and hence will be read as $B2), and there will be 100
cells with amounts in them spreading across columns C->D-

E.... etc, and I need to copy and paste the formula

across these.

Help!!!

 

[GB]

Gary, what is wrong with changing the first N function into the COUNTA
function, ie:

=SUMPRODUCT(COUNTA(INDIRECT("Sheet"&{1,2,3}&"!B2"))*N(INDIRECT("Sheet"&{1,2,
3}&"!C2")))

(You need to change 1,2,3 to whatever no of worksheets you have - 30?)

Geoff


Yesterday I posted the question:

Is there a quicker way of summing the multiplication of 2
values in each sheet over a number of sheets?

i.e.
=Sheet1!B2*Sheet1!C2+Sheet2!B2*Sheet2!C2+Sheet3!B2*Sheet3!
C2+Sheet4!B2*Sheet4!C2+Sheet5!B2*Sheet5!C2+...+Sheet29!
B2*Sheet29!C2+Sheet30!B2*Sheet30!C2


And i got the answer:

=SUMPRODUCT(N(INDIRECT("Sheet"&{1,2,...,30}&"!B2")),
N(INDIRECT("Sheet"&{1,2,...,30}&"!C2")))


Which works!!!

But suppose I have the following:

Sheet1:

B2: a
C2: £10


Sheet2:

B2: b
C2: £11


Sheet3:

B2:
C2: £15

and so on up to sheet 30.

Now if B2 contains text, this acts as a "1", and if not,
then it acts as a zero. So the formula for the above
would be:

=1*10 + 1*11 + 0*15
=21

At the moment, I currently have this as the following
formula:

=COUNTA(Sheet1!B2)*Sheet1!C2+COUNTA(Sheet2!B2)*Sheet2!
C2+COUNTA(Sheet3!B2)*Sheet3!C2+...+COUNTA(Sheet30!B2)
*Sheet30!C2

How could I make this a SUMPRODUCT, or something else that
is shorter?

 

[GB]

Hi

When sheets Sheet1...Sheet30 are in contionous range (no other sheet tabs
between them), then add a column with product (pe.e. X, X2=B2*C2, it
doesn't take much time really - a copy-paste thing) into every sheet (you
can hide that column then), and use the formula

=SUM(Sheet1:Sheet30!X2)


Arvi Laanemets

I agree. I don't see why people wrack their brains over complicated formulae
when it can be done easily this way. It's especially easy if you group the
worksheets before dropping the formula into X2. It also leaves a clear trail
of what you have done for the next person to follow - which might be you in
six months time by which time you may have forgotten what all this is about.

Incidentally, the formula the OP wants here is probably =Counta(B2)*C2

What does pe.e mean?

Regards

GB

 

[GB]

This is difficult, as I have one fixed cell (which will be
B2, and hence will be read as $B2), and there will be 100
cells with amounts in them spreading across columns C->D-

E.... etc, and I need to copy and paste the formula

across these.

Help!!!

------------------------------------------------------------------

Gary, you can group the worksheets together for editing. Just click on the
sheet 1 tab, hold down shift and click on sheet 30. Now all your edits will
apply to all 30 sheets at once.

In cell C3 (or choose whatever row number you want) enter =Counta($B2)*C2
Then copy that across all 100 columns

You presumably have a summary sheet somewhere. Click on that sheet's tab, so
it is the only one selected now. In cell C3 enter =SUM(Sheet1:Sheet30!C3),
then copy that across all 100 columns you are interested in.

Alternatively, you can use the formula in my other post, ie :

=SUMPRODUCT(COUNTA(INDIRECT("Sheet"&{1,2,3}&"!$B2"))*N(INDIRECT("Sheet"&{1,2
,
3}&"!C2")))

(You need to change 1,2,3 to whatever no of worksheets you have - 30?)

I prefer the first approach because it is much easier to see what you have
done when you come to revisit the spreadsheet in the future - at least I
think it is!

Geoff

 

[J.E. McGimpsey]

Sometimes people rack their brains over complicated formulas to
avoid inadvertent errors on simple ones.

Using

=SUM(Sheet1:Sheet3!A1)

is both simple and effective *if* the objective is to sum A1 in the
sheets *by position*. But if the objective is to sum cell A1 on
Sheet1, Sheet2 and Sheet3 only, the formula gives no error or
indication of a problem if, say, the user moves Sheet2 from between
Sheet1 and Sheet3, or if sheet "harry" is inserted between Sheet1
and Sheet3.

Or, depending on how the sheets are moved, the formula may be
modified without notice. For instance, if Sheet1 is moved to the
right of Sheet3, XL silently converts the formula to
=SUM(Sheet2:Sheet3!A1), but does not convert back if Sheet1 is
returned to its original position.

Nor does it offer any basis for the "next person" to be able to
discern the validity or lack thereof. In the example above,
=SUM(Sheet2:Sheet3!A1) does not look, per se, clearly more or less
correct than =SUM(Sheet1:Sheet3!A1).

 

[GB]

Sometimes people rack their brains over complicated formulas to
avoid inadvertent errors on simple ones.

Using

=SUM(Sheet1:Sheet3!A1)

is both simple and effective *if* the objective is to sum A1 in the
sheets *by position*. But if the objective is to sum cell A1 on
Sheet1, Sheet2 and Sheet3 only, the formula gives no error or
indication of a problem if, say, the user moves Sheet2 from between
Sheet1 and Sheet3, or if sheet "harry" is inserted between Sheet1
and Sheet3.

Or, depending on how the sheets are moved, the formula may be
modified without notice. For instance, if Sheet1 is moved to the
right of Sheet3, XL silently converts the formula to
=SUM(Sheet2:Sheet3!A1), but does not convert back if Sheet1 is
returned to its original position.

Nor does it offer any basis for the "next person" to be able to
discern the validity or lack thereof. In the example above,
=SUM(Sheet2:Sheet3!A1) does not look, per se, clearly more or less
correct than =SUM(Sheet1:Sheet3!A1).

Very good point!

Of course, it could work the other way around, too. User deliberately
inserts sheet 'harry' between sheet1 and sheet3 because he wants it
included in the summation. So, the simpler sum formula works in the way the
user expects, whereas the more complex formula has to be edited before it
will do what he wants.

GB