MAX across different worksheets

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Daesthai - 16 Sep 2007 23:34 GMT

What would be the proper syntax to sume the largest number from the same
range of cells on different sheets? I've tried a couple different ways, none
have worked yet. Here's the current version, but I'm not sure what part of
it is wrong.

=SUM('Jan:Jun(MAX(C19:C1047))

Reply to this Message

Franz Verga - 16 Sep 2007 23:50 GMT

Nel post:37514633-5E92-4C83-A314-FCA086CCEC69@microsoft.com,
Daesthai <Daesthai@discussions.microsoft.com> ha scritto:

> What would be the proper syntax to sume the largest number from the
> same range of cells on different sheets? I've tried a couple
> different ways, none have worked yet. Here's the current version,
> but I'm not sure what part of it is wrong.
>
> =SUM('Jan:Jun(MAX(C19:C1047))

Hi Daesthai,

I think in this case you should use a formula like this:

=SUM(MAX(Foglio1!A1:A8),MAX(Foglio2!A1:A8),MAX(Foglio3!A1:A8))

Signature

(I'm not sure of names of menus, options and commands, because
translating from the Italian version of Excel...)

Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

Reply to this Message

JMB - 18 Sep 2007 04:36 GMT

You could put the MAX function on each worksheet (let's say C18) and use
=SUM(Jan:Jun!C18)

I like to put empty worksheets called START and END on either side of the
target worksheets and use
=SUM(Start:End!C18).

For something different, this also seems to work okay
=SUMPRODUCT(SUBTOTAL(4,INDIRECT({"Jan","Feb","Mar","Apr","May","Jun"}&"!C19:C1047")))

but I have to caution that this formula will not update if your target
ranges move (or have rows/columns inserted in the data). Also, Indirect is
volatile (it recalculates every time excel does), so too many of these will
adversely affect performance.

> What would be the proper syntax to sume the largest number from the same
> range of cells on different sheets? I've tried a couple different ways, none
> have worked yet. Here's the current version, but I'm not sure what part of
> it is wrong.
>
> =SUM('Jan:Jun(MAX(C19:C1047))

Reply to this Message

Daesthai - 19 Sep 2007 14:06 GMT

Thank you both!

> You could put the MAX function on each worksheet (let's say C18) and use
> =SUM(Jan:Jun!C18)
[quoted text clipped - 17 lines]
> >
> > =SUM('Jan:Jun(MAX(C19:C1047))

Reply to this Message