MAX across different worksheets

[Guest]

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))

 

[Franz Verga]

Nel post:[email protected],

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))


--
(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

 

[Guest]

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.

 

[Guest]

Thank you both!

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.